Writing a query

[former_member398983]

I am new to SAP sourcing and have a requirement to write a query with the below result fields. The Query must show all the RFXs’s. If the RFX has a related project, that project should be displayed as well and the same should be done if it has a related master agreement. The important part that I seem to struggle with is that the query brings duplicates once the RFX has a Related Project and is created from another RFX meaning it will also have a related RFX.  Below is the query that I wrote and attached the picture of my result fields. Please assist

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_PRO_PROJECTS T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T2 ON T2.PARENT_OBJECT_ID = T1.OBJECTID

RIGHT OUTER JOIN <%SCHEMA%>.FCI_RFX_DOC T3 ON T3.OBJECTID = T2.LINK_DOC_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T10 ON T10.PARENT_OBJECT_ID = T3.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_CONTRACT T11 ON T11.OBJECTID = T10.LINK_DOC_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>. FCI_CONGEN_CONTRACT_DOC T13 ON T13.PARENT_OBJECT_ID = T11.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_CONTRACT_PHASE T14 ON T14.PARENT_OBJECT_ID = T13.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_WORKFLOW_HISTORY T15 ON T15.PARENT_OBJECT_ID = T13.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(projects.projects)%> T5 ON T1.OBJECTID = T5.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T6 ON UPPER(T6.DISPLAY_NAME) = UPPER(T5.EXT_CLASSIFICA_NEW_OBJECT_NAME)

LEFT OUTER JOIN <%SCHEMA%>. FCI_PRO_CONFIG_PHASE_SUB T17 ON T1.CURR_CONFIG_PHASE_OBJECT_ID = T17.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>. FCI_DOC_WORKFLOW_HISTORY T18 ON T18.PARENT_OBJECT_ID = T1.OBJECTID

LEFT OUTER JOIN <%EXT_TABLE(rfx.RFXDoc)%> T8 ON UPPER(T6.DISPLAY_NAME) = UPPER(T8.EXT_CLASSIFICA_NEW_OBJECT_NAME)

LEFT OUTER JOIN <%EXT_TABLE(rfx.RFXDoc)%> T16 ON T3.OBJECTID = T16.PARENT_OBJECT_ID

WHERE (T3.IS_TEMPLATE = 0 AND T3.CONTEXTID=<%CONTEXT(rfx.RFXDoc)%> AND T3.INACTIVE = 0 )

AND  (T13.CURR_CONFIG_PHASE_OBJECT_ID = T14.OBJECTID OR T13.CURR_CONFIG_PHASE_OBJECT_ID IS NULL)

AND  (T14.DISPLAY_NAME = T15.PHASE_REF_OBJECT_NAME OR T14.DISPLAY_NAME IS NULL)

AND (T18.PHASE_REF_OBJECT_NAME = T17.DISPLAY_NAME OR T17.DISPLAY_NAME IS NULL)

AND (T2.LINK_DEFINITION_OBJECT_NAME = 'Related RFx' AND

T10.LINK_DEFINITION_OBJECT_NAME <> 'RFx Template' and T10.LINK_DEFINITION_OBJECT_NAME = 'Related Project' OR T2.LINK_DEFINITION_OBJECT_NAME IS NULL)

and (T10.LINK_DEFINITION_OBJECT_NAME <> 'Previous RFx' or T10.LINK_DEFINITION_OBJECT_NAME is null)

and (T10.LINK_DEFINITION_OBJECT_NAME <> 'Related Agreement' or T10.LINK_DEFINITION_OBJECT_NAME is null)

AND (T10.PARENT_DOC_NAME = T3.UNIQUE_DOC_NAME OR T10.PARENT_DOC_NAME IS NULL)

AND (DATE(T3.CREATED_AT) >= ? AND DATE(T3.CREATED_AT) <= ?)

[Former Member · Accepted solution]

Hello Mashimbye,

Please find below query which will pull all the RFxs along with the documents(projects, RFx, Auctions, MAs) attached in the Documents links section of the RFx

Query String:

SELECT DISTINCT

<%RESULTS%>

FROM

<%SCHEMA%>.FCI_RFX_DOC T1

LEFT OUTER JOIN <%SCHEMA%>.FCI_DOC_LINK T5

ON T1.OBJECTID = T5.PARENT_OBJECT_ID

WHERE T1.INACTIVE = 0

AND T1.IS_TEMPLATE = 0

AND T1.CONTEXTID=<%CONTEXT(rfx.RFXDoc)%>

Result Field: Add a column in the result field with Database Column name as below

FCI_GET_COMMA_LIST('SELECT T5.LINK_DOC_ID FROM <%SCHEMA%>.FCI_DOC_LINK T5 WHERE (T5.PARENT_CLASS_ID = 900 OR T5.PARENT_CLASS_ID = 1100 OR T5.PARENT_CLASS_ID = 1004 OR T5.PARENT_CLASS_ID = 816) AND T5.PARENT_OBJECT_ID='||T1.OBJECTID)

This resolves your issue!

Regards,

Vignesh

[former_member398983 · Accepted solution]

Hi Reddy

Thanks for the response. The Oracle function 'FCI_GET_COMMA_LIST' gives me the below error when I add it to the result field.

SQL Exception: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FCI_GET_COMMA_LIST;FUNCTION, DRIVER=4.14.137, ANSI-92 SQL State: 42884, Vendor Error Code: -440. [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FCI_GET_COMMA_LIST|FUNCTION, DRIVER=4.14.137, 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=4.14.137, ANSI-92 SQL State: 56098, Vendor Error Code: -727.

[former_member190023 · Accepted solution]

I would recommend using LISTAGG:

LISTAGG

Regards,

Bogdan

[Former Member · Accepted solution]

Hello Mashimbye,

The query is perfectly working for me. Attaching the screenshots.

Regards,

Vignesh

[former_member398983 · Accepted solution]

Hi Vignesh

I really do appreciate your help. Below are my screenshorts. I can't find anything different but mine gives an error.

[Former Member · Accepted solution]

Hello Mashimbye,

Which version of Sourcing you are in?
What datatype you mentioned for test column, in the results field tab?

Regards,

Vignesh

[former_member398983 · Accepted solution]

I used string for the Data Type. The Version is Version 10, Service pack 7

[Former Member · Accepted solution]

Hello Mashimbye,

The same query is working fine for me but I am not sure why it is not working for you.

As a work around, please delete the column test from Results field tab and add one new column with Database Column name as T5.LINK_DOC_ID. This will pull all the RFxs along with its document links in different lines.

You will get the preview in below format:

RFX1     doclink1

RFX1     doclink2

RFX1     doclink3

RFX1     doclink4

Regards,

Vignesh

[former_member398983 · Accepted solution]

Hi Vignesh

All your answers are correct but my initial query does that and that is what I have to fix. But In short, My result should be in the format below: In different result fields.

RFX1  | Doclink1 | Doclink2 | Doclink3|

RFX2  | Doclink1 | Doclink2 | Doclink3|

RFX3  | Doclink1 | Doclink2 | Doclink3|

Thanks for your patience and assistance. Much appreciated

Regards