How to select only first row on a query

[isaac_ariza_cruz]

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:

• SELECT FIRST 1 ....
• SELECT TOP 1
  
• ... WHERE ROWNUM = 1
• ...

... 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

[isaac_ariza_cruz · Accepted solution]

Hi again,

I've solve this issue in the following way:

Query string:

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%>

Result fields:

(SELECT FIELD1 FROM <%EXT_TABLE(projects.projects,z_custom)%> T2 WHERE T2.PARENT_OBJECT_ID=T1.OBJECTID FETCH FIRST 1 ROWS ONLY)

Hope this info helps to those who need it.

Regards!