Dynamic Item Description Based on Attributes - Derived Property, FOR XML PATH not working -- help?

Greetings!

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!

(SELECT
STUFF(
(SELECT ' ' + AttributeValue
FROM AttributeValueView avv
WHERE avv.RefRowPointer = RowPointer AND avv.AttributeValue IS NOT NULL
FOR XML PATH(''), TYPE).value('(./text())[1]', 'nvarchar(max)'),1,1,''))

Find more posts tagged with

Accepted answers

All comments

Legacy Contributor

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

chris

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.

Legacy Contributor

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.

Legacy Contributor

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

Copyright © 2025 Infor. All rights reserved.