Skip to Content
0
Nov 23, 2012 at 10:23 AM

How to select only first row on a query

2558 Views

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