cancel
Showing results for 
Search instead for 
Did you mean: 

How to define a query with custom fields via Query Definition?

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

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

Former Member
0 Kudos

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

Former Member
0 Kudos

Waldemar,


I feel you have given incorrect column name.

Go to setup->Browse extension schema->Master Agreement.

You can find the actual column name of the field ZCLM_DATRESUB.

Regards,

Vignesh

Former Member
0 Kudos

thank you very much !

it works

Former Member
0 Kudos

Waldemar,

you're welcome!

Could you please mark my response(correct answer) as CORRECT, you have mistakenly marked your reply as correct.

This will help the SCN user to find the correct reply.

Regards,

Vignesh

Former Member
0 Kudos

done

Answers (0)