Update date variable in where clause coming from formula tool

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

Dynamic Processing

Accepted answers

fmvizcaino

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

2020-04-22 Update date variable in where clause coming from formula tool.yxwz

All comments

fmvizcaino

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

2020-04-22 Update date variable in where clause coming from formula tool.yxwz

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

Action Tool Start Dt.jpg Formula Tool Query Actual.jpg .

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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335