I am attempting to create a derived field in CSI SLItems that will generate a custom Item description dynamically, based on Item Attributes. Because multiple attributes can exist per Item, I am needing to get columns from multiple rows and flatten them to fit in one row, space delimited.
However, the code below is yielding 'Incorrect syntax near the keyword 'NULL'. Any assistance would be greatly appreciated!! Workaround also welcome. Thank you!
(SELECTSTUFF( (SELECT ' ' + AttributeValue FROM AttributeValueView avv WHERE avv.RefRowPointer = RowPointer AND avv.AttributeValue IS NOT NULL FOR XML PATH(''), TYPE).value('(./text())', 'nvarchar(max)'),1,1,''))
Hi, if it's inside a SP, I would make a cursor and concatenate each row in a string. But if it's supposed to be dynamic in just one sentence... will have to investigate :)
Maybe I'm throwing folks off with the 'dynamic' wording. I just need the derived property to work, and by nature of being a derived field, it will change when the attributes change. Sorry for the confusion.
It would be more helpful to know the logic behind the selection - which attributes or specific attribute, key ID(s) associated with those attributes, etc. - to help guide you in how to configure the derived property. I would also advise creating the solution as an IDO Extension Class (.NET Code) and not SQL, as stored procedures are deprecated in future versions of CSI. Depending on the complexity of your logic, it may be able to be done directly in the implementation of the derived property though.
So assuming all of that embeds into the full SQL statement without issue, It is possible underlying framework may be attempting to translate NOT NULL to COALESCE. In which case there is a risk that this is failing because of the complexity https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver15
Is this reproducible in a local dev environment in which case you might be able to add a wire tap and see the generated SQL statement https://youtu.be/MM5vy6y6huI