cancel
Showing results for 
Search instead for 
Did you mean: 

Query Error

Former Member
0 Kudos

Hi expert,

I'm trying to run the below query, I've tried every trick I know but my attempts have returned with no luck.

See Query code below and Query Error.

Query Code:

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

LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T4

ON T1.INT_CAT_OBJECT_ID = T4.OBJECTID

<%DYN_JOIN%>

WHERE T1.CONTEXTID=<%CONTEXT(userdefined.bizdoc1)%>

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

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

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

<%AND <%SCHEMA%>.FCI_DOC_CHECKCOLLAB(T1.OBJECTID, 1902,<%CURRENT_USER_ID%>,'<%CURRENT_USER_GROUPS%>') = 1

<%AND_OP((<%?(MyOwnership)%>=2) OR

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%DYN_FILTER%>

<%ORDERBY%>

Query Error:


SQL Exception: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<%;(T1.IS_TEMPLATE) = ?;<space>, DRIVER=4.19.26, ANSI-92 SQL State: 42601, Vendor Error Code: -104. DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<%;(T1.IS_TEMPLATE) = ?;<space>, DRIVER=4.19.26 [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;<%|(T1.IS_TEMPLATE) = ?|<space>, DRIVER=4.19.26, ANSI-92 SQL State: 56098, Vendor Error Code: -727. [Nested Exception] SQL Exception: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-104;42601;<%|(T1.IS_TEMPLATE) = ?|<space>, DRIVER=4.19.26, ANSI-92 SQL State: 56098, Vendor Error Code: -727.

Accepted Solutions (1)

Accepted Solutions (1)

former_member190023
Contributor
0 Kudos

Hi Sabelo,

Firstly, the AND attribute syntax is incorrect. It should have the below form:

<%AND("_complex_query_")%>


Second, the call to FCI_DOC_CHECKCOLLAB check function will not work for class 1902 - because that is just an Enum.

As recommendation, if you want to display UDO1s only where current user is collaborator (or owner), instead of calling the checkcollab function, you could simply add a new join like below:

JOIN <%COLLABCHECK_TBL(T1.OBJECTID, 9999101)%>

Former Member
0 Kudos

Hi Bogdan,

Thank you for your response, I managed to sort the error out and now everything runs smoothly.

Check it out:

SELECT <%RESULTS%> FROM <%SCHEMA%>.FCI_UDEF_BIZDOC1 T1 LEFT OUTER JOIN <%SCHEMA%>.FCI_MAS_INTERNAL_CAT T4 ON T1.INT_CAT_OBJECT_ID = T4.OBJECTID

AND <%SCHEMA%>.FCI_DOC_CHECKCOLLAB(T1.OBJECTID, 9999101,<%CURRENT_USER_ID%>,'<%CURRENT_USER_GROUPS%>') = 1

AND ((<%?(MyOwnership)%>=2) OR

(T1.DOC_OWNER_USER_OBJECT_ID=<%CURRENT_USER_ID%>)) <%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%DYN_JOIN%>

WHERE T1.CONTEXTID=<%CONTEXT(userdefined.bizdoc1)%>

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

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

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

<%AND_OP("T1.INT_CAT_OBJECT_ID",<%?(T1.INT_CAT_OBJECT_ID)%>)%>

<%AND_OP("T1.INACTIVE",<%?(T1.INACTIVE)%>)%>

<%DYN_FILTER%>

<%ORDERBY%>

Thanks again.

Answers (0)