How to include where condition of type VARCHAR2 in FCI_GET_COMMA_LIST

[Former Member]

Hi,

         I am trying hard to add where condition of type string in FCI_GET_COMMA_LIST but it is giving me error.

Below query works

SELECT T1.UNIQUE_DOC_NAME,

FCI_GET_COMMA_LIST('SELECT DISPLAY_NAME FROM FCI_DOC_COLLABORATOR WHERE COLLABORATOR_ROLE_OBJECT_ID=-2147483246 AND PARENT_OBJECT_ID=' || T1.OBJECTID)

FROM SAPSRSDB.FCI_PRO_PROJECTS T1

But below query gives error "Missing right parenthesis"

SELECT T1.UNIQUE_DOC_NAME,

FCI_GET_COMMA_LIST('SELECT DISPLAY_NAME FROM FCI_DOC_COLLABORATOR WHERE collaborator_role_object_name like 'sdf' AND PARENT_OBJECT_ID=' || T1.OBJECTID)

FROM SAPSRSDB.FCI_PRO_PROJECTS T1

Here collaborator_role_object_name is of type VARCHAR2

Please help me in this regard.

Many Thanks,

Srini

[vikram_shanmugasundaram · Accepted solution]

Hi Srinivas,

Can you try the below...

SELECT T1.UNIQUE_DOC_NAME, FCI_GET_COMMA_LIST('SELECT DISPLAY_NAME FROM FCI_DOC_COLLABORATOR WHERE collaborator_role_object_name like ''sdf'' AND PARENT_OBJECT_ID=' || T1.OBJECTID) FROM FCI_PRO_PROJECTS T1

And btw, if you are running on Oracle you may want to try using the wm_concat which is Oracle's native functions and is supposed to be more efficient than FCI_GET_COMMA_LIST.

Regards,

Vikram

[Former Member · Accepted solution]

Thanks Vikram