How to subtract one day from the current day for jobs running daily using oracle query in tdbinput component

[AAA999]

Guys

We have  jobs that will run daily and monthly. For daily jobs  it needs to minus/subtract one day with the current day. We tried by passing the context variable in the sql query but it is not working. is there something we are missing?

The same query runs fine if I run the sql query in toad. The oracle column is timestamp. Any help will be appreciated.

We have enterprise studio

Process:

Created 2 Context Variable one with string Type and other with date Type

1) context.TheDate   string  TalendDate.formatDate("yyyy-MM-dd",TalendDate.getCurrentDate())

2) context.TheDate1   Date  TalendDate.getCurrentDate()

At run time the context variables are not replaced with the values defined

query used in oracle component

SELECT NAME FROM TEST

WHERE
TO_CHAR(TRUNC(UPDATE_DATE),'YYYY-MM-DD') >= '"+context.TheDate+"' ----String context variable

replaced the where clause with date  context variable 2

TRUNC(UPDATE_DATE) >= '"+context.TheDate1+"'--------------------------Date context variable

;

Oracle query works fine in toad:

SELECT NAME FROM TEST

WHERE TRUNC(UPDATE_DATE) >= TO_DATE('column name', 'YYYY-MM-DD')

Thanks

[manodwhb]

In Talend you need use below expression.

TalendDate.formatDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.getCurrentDate(), -1, "dd"))

As part testing with you context variable concept I suggest you to print in tjava and see it that correctly printing the query if not need to modify.
You need to check for the data type of context variable.

[AAA999]

Thanks for the quick reply. 

 I passed the logic you provided in the date type context variable but getting error when running the job

TalendDate.formatDate("yyyy-MM-dd", TalendDate.addDate(TalendDate.getCurrentDate(), -1, "dd"))

ora-01858 a non numeric character was found where a numeric was expected

WHERE
TRUNC(UPDATE_DATE) >= '"+context.TheDate1+"'.

Can you advice if i missed something.

I have also opened another subject but haven't received any real solution, I am new to Talend and would appreciate if you can advice how to achieve it. I have attached the various scenarios I used for email in the subject. below is the link for the subject. It would be great if you can provide a design. Again thanks a lot.

Send email when using sequence jobs

https://community.talend.com/t5/Design-and-Development/Send-email-when-using-sequence-jobs/m-p/21819...