Hi experts,
Due to a requirement, we would need to retrieve only the first row of a collection created at project document type. For that reason, we have created (as example) following query:
SELECT
<%RESULTS%>
FROM
<%SCHEMA%>.FCI_PRO_PROJECTS T1
WHERE
T1.INACTIVE = 0
AND T1.IS_TEMPLATE = 0
AND T1.CONTEXTID = <%CONTEXT(projects.projects)%>
AND T1.OBJECTID = <%?(CurrentDocument)%>
<%ORDERBY%>
In the RESULT FIELDS tab, we've created one field as follow: (SELECT FIRST(T2.FIELD) FROM <%EXT_TABLE(projects.projects,z_custom)%> T2 WHERE T2.PARENT_OBJECT_ID = T1.OBJECTID)
However, it raises following error:
SQL Exception: DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FIRST;FUNCTION, DRIVER=3.62.56, ANSI-92 SQL State: 42884, Vendor Error Code: -440. DB2 SQL Error: SQLCODE=-440, SQLSTATE=42884, SQLERRMC=FIRST;FUNCTION, DRIVER=3.62.56 [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-440;42884;FIRST|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;FIRST|FUNCTION, DRIVER=3.62.56, ANSI-92 SQL State: 56098, Vendor Error Code: -727.
We've also tried with other SQL statements like:
... but similar errors are raised.
Could you please help us explaining us how this behaviour could be covered?
Thanks in advance for the help and time spent to support it.
Best regards,
Isaac