Skip to Content
0
Former Member
Jul 06, 2015 at 01:51 PM

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

22 Views

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