Advanced Reporting Concepts - Showing a Comma Separated List of Values

[isaac_ariza_cruz]

Hi Sourcing experts,

I would need your help regarding the following topic:

In queries, it is possible to to show a comma separated list of values in a single cell in a result set. For example: show all of the collaborators (comma separated) on a document when querying over a list of documents.

I know we can use function FCI_GET_COMMA_LIST to do it but I've been trying to use it in different ways but following error has been retrieved in all the cases:

SQL Exception: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FCI_GET_COMMA_LIST;FUNCTION, DRIVER=3.62.56, ANSI-92 SQL State: 42884, Vendor Error Code: -440. DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FCI_GET_COMMA_LIST;FUNCTION, DRIVER=3.62.56 [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FCI_GET_COMMA_LIST|FUNCTION, DRIVER=3.62.56, ANSI-92 SQL State: 56098, Vendor Error Code: -727. [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FCI_GET_COMMA_LIST|FUNCTION, DRIVER=3.62.56, ANSI-92 SQL State: 56098, Vendor Error Code: -727.

Could you please provide me any easy example on how this function should be used? It would be really appreciated.

Thanks in advance and best regards ,

Isaac

[Former Member · Accepted solution]

Hi Isaac,

In the results field database column name try the following sort of thing:

FCI_GET_COMMA_LIST('SELECT T4.VENDOR_OBJECT_NAME FROM <%EXT_TABLE(projects.projects,client_sensitivity)%> T4 WHERE T4.PARENT_OBJECT_ID='||T1.OBJECTID)

In the example it does a csv list of vendors from a extension collection (table T4) on my project (table T1).

I hope this helps.

Dan

[isaac_ariza_cruz · Accepted solution]

Hi Dan,

Thanks a lot for the answer and support. It was helpful.

Thanks again and best regards,

Isaac

[former_member398983 · Accepted solution]

Hi

I see you have assisted with the correct answer to the question. Please also assist her. I have the below statement on the result field of my query but I get the same error. What could be wrong with my statement?

FCI_GET_COMMA_LIST('SELECT T5.LINK_DOC_ID FROM <%SCHEMA%>.FCI_DOC_LINK T5 WHERE T5.PARENT_OBJECT_ID='||T1.OBJECTID)