Query throwing error on personalization

[Former Member]

Hi,

We are on  SAP Sourcing 7.0 SP03

Developed a custom query

SELECT

<%RESULTS%>

FROM

(

SELECT

T1.DISPLAY_NAME AS AUNIQUE_DISPLAY_NAME,

<%LOAD_PAGE(contracts.Agreement,T1.OBJECTID )%> AS AGREEMENT_LINK,

T1.UNIQUE_DOC_NAME AS CUNIQUE_DOC_NAME ,

1003 AS CLASSID,

E1.ZCLMSCDSUPPNAME AS SUPPLIER_ID,

T1.VENDOR_OBJECT_NAME AS VENDOR_OBJECT_NAME,

T14.DISPLAY_NAME AS ZCOMPANY ,

E1.ZCLMSCDCONVERTED AS CONVERTED ,

T13.DISPLAY_NAME AS STATUS ,

T1.EFFECTIVE_DATE_DATE AS EFFECTIVE_DATE,

T1.EXPIRATION_DATE_DATE AS EXPIRATION_DATE,

T1.DOC_OWNER_USER_OBJECT_NAME AS AGREEMENT_MANAGER ,

E1.ZCLMSCDESTCTRVAL_PRICE AS EST_CONTRACT_VALUE

FROM

<%SCHEMA%>.FCI_AGREEMENT T1

LEFT OUTER JOIN <%COLLABCHECK_TBL(T1.OBJECTID, 1003)%>

LEFT OUTER JOIN <%EXT_TABLE(contracts.Agreement)%> E1

ON T1.OBJECTID = E1.PARENT_OBJECT_ID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T13

ON T1.STATUS_OBJECT_ID = T13.OBJECTID

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_VALUE_LIST_VALUE T14

ON E1.ZCLMSCDCOMPANY_OBJECT_ID = T14.OBJECTID

<%DYN_JOIN%>



WHERE

T1.INACTIVE = 0

AND T1.IS_TEMPLATE =0

AND T1.CONTEXTID = <%CONTEXT(contracts.Agreement)%>

AND T1.PARENT_CLASS_ID = 1004

AND T1.PARENT_COLLN_ID = 1

<%AND_OP("UPPER(T1.DISPLAY_NAME)",<%?(T1.DISPLAY_NAME)%>)%>


<%AND_OP("UPPER(T1.VENDOR_OBJECT_NAME)",<%?(T1.VENDOR_OBJECT_NAME)%>)%>

<%AND_OP("UPPER(T1.DOC_OWNER_USER_OBJECT_NAME)",<%?(T1.DOC_OWNER_USER_OBJECT_NAME)%>)%>

<%AND_OP("E1.ZCLMSCDCONVERTED",<%?(E1.ZCLMMSACONVERTED)%>)%>


<%AND_OP("T14.OBJECTID",<%?(T14.OBJECTID)%>)%>

<%AND_OP("T13.OBJECTID",<%?(T13.OBJECTID)%>)%>

<%AND_OP("E1.ZCLMSCDESTCTRVAL_PRICE",<%?(E1.ZCLMSCDESTCTRVAL_PRICE)%>)%>

<%DYN_FILTER%>

<%ORDERBY%>

)

Then

We added the query in query group and marked for SRM Navigation

We executed the query from SRM Navigation.

Got the output, then we personalised the

output to display 1 more custom field.

We are getting error

SQL Exception: ORA-00904:"D1"."DISPLAY_NAME_ID": invalid identifier

But we do not have D1 in our query string or result or filters.

Can anyone point out the issue ?

Regards.

[Former Member]

Hi Vaibhav,

Your getting "SQL Exception: ORA-00904:"D1"."DISPLAY_NAME_ID": invalid identifier"  error because in the query string itself your selecting particular fields and using personalization you have tried to display extra filed which is not mentioned in the query string that is why your getting SQL exception.

And D1 is coming because by using personalization you tried to display Extension Collection filed(CUSTOM Field).

To resolve the SQL exception remove selection of particular fields in the query string it self and get the required fields using result field tab.

I hope above information will clarify your doubts.

Regards,

Lava

[Former Member]

Vaibhav,

Often (not sure if "always") it shows the raw sql it generated above the error message.  You can search this for "D1." and it may show you were the problem lies.

If you see it in the generated SQL and want to post what you see generated here, I may be able to point you in the right direction.

Thanks,

Mike

[Former Member]

Hi Vaibhav,

Did you check on the "Results Field" tab of the Query Definition, if there are any references to "D1.DISPLAY_NAME_ID".

If so, then that could be the reason for the error that you are getting.

Regards,

Parankush

[Former Member]

Parankush,

Thanks for your reply, but i have checked everything : query string, filters and result fields.

no where D1 alias is there.

Regards,

Vaibhav

[Former Member]

Hi Vaibhav,

I am facing same issue  do you have solution for that. please share with me.