Question Re: Advanced Reporting Concepts - Showing a Comma Separated List of Values in Spend Management Q&A

Advanced Reporting Concepts - Showing a Comma Separated List of Values

isaac_ariza_cruz — Fri, 16 Nov 2012 14:29:41 GMT

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

Re: Advanced Reporting Concepts - Showing a Comma Separated List of Values

Former Member — Fri, 16 Nov 2012 18:38:43 GMT

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

Re: Advanced Reporting Concepts - Showing a Comma Separated List of Values

isaac_ariza_cruz — Fri, 23 Nov 2012 09:19:29 GMT

Hi Dan,

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

Thanks again and best regards,

Isaac

Re: Advanced Reporting Concepts - Showing a Comma Separated List of Values

former_member398983 — Wed, 14 Oct 2015 15:17:12 GMT

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)