on 07-06-2015 2:51 PM
Hi,
i have a task to define a query with custom fields. The query is now like this:
SELECT <%RESULTS%>
FROM <%SCHEMA%>.FCI_CONTRACT T2
WHERE T2.INACTIVE = 0
AND T2.IS_TEMPLATE = 0
AND T2.EXPIRATION_DATE_DATE IS NOT NULL
AND T2.EXPIRATION_DATE_DATE >= <%TODAY%>
AND T2.CONTEXTID=<%CONTEXT(contracts.Contract)%>
<%ORDERBY%>
this query works fine and i get all contracts that not expired.
now i wanna refine the query (get a subquery) with all contracts they not expired and they have "today date" in my custom field.
the name of my custom field is: RESUBMISSION_DATE. So i need something like this:
SELECT <%RESULTS%>
FROM <%SCHEMA%>.FCI_CONTRACT T1
WHERE T1.INACTIVE = 0
AND T1.IS_TEMPLATE = 0
AND T1.EXPIRATION_DATE_DATE IS NOT NULL
AND T1.EXPIRATION_DATE_DATE >= <%TODAY%>
AND T1.RESUBMISSION_DATE = <%TODAY%>
AND T2.CONTEXTID=<%CONTEXT(contracts.Contract)%>
<%ORDERBY%>
but T1 hasn't a field like RESUBMISSION_DATE.
can someone help me with this?
thanks
Waldemar
Hello,
You need to make use of custom table in this query.
SELECT
<%RESULTS%>
FROM
<%SCHEMA%>.FCI_CONTRACT T1
LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract)%> E1
ON T1.OBJECTID = E1.PARENT_OBJECT_ID
WHERE
T1.INACTIVE = 0
AND T1.IS_TEMPLATE = 0
AND T1.EXPIRATION_DATE_DATE IS NOT NULL
AND T1.EXPIRATION_DATE_DATE >= <%TODAY%>
AND E1.RESUBMISSION_DATE = <%TODAY%>
AND T1.CONTEXTID=<%CONTEXT(contracts.Contract)%>
This will solve your issue!
Regards,
Vignesh
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vignesh
thank you for your answer.
Like you suggested, i have defined my query like this:
SELECT <%RESULTS%>
FROM <%SCHEMA%>.FCI_CONTRACT T1
LEFT OUTER JOIN <%EXT_TABLE(contracts.Contract)%> E1
ON T1.OBJECTID = E1.PARENT_OBJECT_ID
WHERE T2.INACTIVE = 0
AND T1.IS_TEMPLATE = 0
AND T1.EXPIRATION_DATE_DATE IS NOT NULL
AND T1.EXPIRATION_DATE_DATE >= <%TODAY%>
AND E1.ZCLM_DATRESUB IS NOT NULL
AND E1.ZCLM_DATRESUB = <%TODAY%>
AND T1.CONTEXTID=<%CONTEXT(contracts.Contract)%>
<%ORDERBY%>
but i get this error message:
SQL Exception: ORA-00904: "E1"."ZCLM_DATRESUB": invalid identifier , ANSI-92 SQL State: 42000, Vendor Error Code: 904. ORA-00904: "E1"."ZCLM_DATRESUB": invalid identifier
the name of the field is ZCLM_DATERESUB and not RESUBMISSION_DATE.
maybe ist the name of the database field not equal to the field definition from the contract?
regards
Waldemar
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.