on 02-22-2016 9:44 AM
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.
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)%>
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.