Update date variable in where clause coming from formula tool

[rakeshkumar801]

I have to build a query in the formula tool and want to replace the variable [START_DT] in where clause of the query to make it dynamic. Please help in replacing  [START_DT]  so that 

Formula Tool query expressionFormula Tool containing query with date variabeWorkflow Picture 

[fmvizcaino · Accepted solution]

Hi @rakeshkumar801 ,

I'm attaching an example showing how to replace the [START_DT] column using the action tool.

Be aware that you are replacing a date column, so if your entry_date_id is in date format you will need the ToDate function to format the string as date for your query to work.

Take a look how I've built the string around the [START_DT]

Best,

Fernando Vizcaino

[rakeshkumar801]

Hi,

I checked "Replace a specific string" put [START_DT] and [END_DT] in their respective text box but the query is giving error. Please help with this.

Error: Dynamic Input (1156): DataWrapOCI: Unable to prepare the query: "select  Transaction_ID,Transaction_sequence,Card_ID,Case_ID,Pan_entrymode_ID,Pin_entrymode_ID,  Institution_ID,Office_ID,Person_ID,Program_ID,Transaction_type_ID,Pre_balance,

Amount,Response_type_ID,Entry_date_ID,to_char(entry_datetime,'YYYY-mm-dd HH:MI:SS AM'),Settlement_date_ID,Settlement_date,Amount_cent,Post_balance,Addr_var_ID,TO_DATE(ENTRY_DATE_ID,'YYYYMMDD'),extract(month from (TO_DATE(ENTRY_DATE_ID,'YYYYMMDD'))),

to_char(to_date(ENTRY_DATE_ID,'YYYYMMDD'),'WW'),EXTRACT(HOUR FROM SETTLEMENT_DATE),count(transaction_id||transaction_sequence) over (partition by case_id,entry_date_id order by entry_datetime RANGE BETWEEN CURRENT ROW AND INTERVAL '15' MINUTE FOLLOWING) Window_Var,

last_day(TO_DATE(ENTRY_DATE_ID,'YYYYMMDD')) from EBTDM.FCT_TRANSACTION_DETAIL_TXNDT where entry_date_id >= [START_DT]  AND entry_date_id < [END_DT]"

Error: ORA-00936: missing expression

.

[fmvizcaino]

Hi @rakeshkumar801 ,

Have you run this specific query in oracle to check if it is working as expected? From what I'm seeing, it looks everything ok.

I would suggest you also run a simple query using the action tool + dynamic input tool to guarantee it is working and then later to build a complex query like that.

Best,

Fernando Vizcaino

[rakeshkumar801]

Thanks for replying.

The simple query is working for other tables and the data is nicely-being pulled. If you see the error message you can that [START_DT] and [END_DT] are being replaced by the numeric value. Can you please check if I am using the syntax correctly.

The query is given below 

"oci:" + [USER] + "/" + [PASSWRD] + "@" + [HOST] + ":" + [PORT] + "/" + [DB] + " | " + " select Transaction_ID,Transaction_sequence,Card_ID,Case_ID,Pan_entrymode_ID,Pin_entrymode_ID, Institution_ID,Office_ID,Person_ID,Program_ID,Transaction_type_ID,Pre_balance,
Amount,Response_type_ID,Entry_date_ID,to_char(entry_datetime,'YYYY-mm-dd HH:MI:SS AM'),Settlement_date_ID,Settlement_date,Amount_cent,Post_balance,Addr_var_ID,TO_DATE(ENTRY_DATE_ID,'YYYYMMDD'),extract(month from (TO_DATE(ENTRY_DATE_ID,'YYYYMMDD'))),
to_char(to_date(ENTRY_DATE_ID,'YYYYMMDD'),'WW'),EXTRACT(HOUR FROM SETTLEMENT_DATE),count(transaction_id||transaction_sequence) over (partition by case_id,entry_date_id order by entry_datetime RANGE BETWEEN CURRENT ROW AND INTERVAL '15' MINUTE FOLLOWING) Window_Var,
last_day(TO_DATE(ENTRY_DATE_ID,'YYYYMMDD')) from EBTDM.FCT_TRANSACTION_DETAIL_TXNDT where entry_date_id >="+"[START_DT] AND entry_date_id <"+"[END_DT]"

[fmvizcaino]

Hi @rakeshkumar801 ,

Your query seems ok, I wasn't able to find any problem in it.

Would you be able to share your workflow with that part in it for me to build something to help you test it?

Best,

Fernando V.

[rakeshkumar801]

Please find the workflow picture attached. The dynamic input tool is showing red mark.

workflow picture