topic Re: Database query result (Dates) set to context variable or better choices? in Talend Studio

Database query result (Dates) set to context variable or better choices?

Anonymous — Mon, 22 Oct 2018 22:11:17 GMT

I'd like to output the result from a SQL query into something like a context variable so it can be used later in the job.

I have done this before with the output as string.

tDBInput---------> tSetGlobalVar

But this time, the query output would be two dates:

"StartDate" and "EndDate".

Then I will use these two dates in another sql query to check an distinct count.

The SQL would be something like:

select count(distinct Order_Date) as cnt from tablename where Order_Date between "StartDate" and "EndDate".

Again, the job will be : tDBInput---------> tSetGlobalVar , this time the count(cnt) will be the global variable.

My question is:

1. Am I doing things correctly here?

2. If I am, how should I refer these "StartDate" and "EndDate" in my 2nd query?

Thanks!

Re: Database query result (Dates) set to context variable or better choices?

Anonymous — Tue, 23 Oct 2018 05:06:47 GMT

Hi,

You can use either tSetGlobalVar or tjavarow to set the data coming out from predecessor component to a context variable.

My understanding is that you are looking for delta data processing and it is the right methodology. Some minor changes can help you to achieve the results.

a) Fetch the start date and end date data as string in specified format or convert the data to string

b) Use a to_date function to convert the string back to date

Please also refer the below post which is doing similar activity.

https://community.talend.com/t5/Design-and-Development/ORA-00933-in-tDBRow-when-using-context-variable/m-p/137861#M85679

Could you please mark the topic as resolved if my answer has helped you? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: Database query result (Dates) set to context variable or better choices?

Anonymous — Tue, 23 Oct 2018 17:55:49 GMT

a) Fetch the start date and end date data as string in specified format or convert the data to string

Should I convert the start_date and end_date into string in my SQL?

Can I output these two as Date first and convert them into string with Talend component? I would prefer this if this could be done.

b) Use a to_date function to convert the string back to date

Thanks!

Re: Database query result (Dates) set to context variable or better choices?

Anonymous — Tue, 23 Oct 2018 18:44:41 GMT

Hi,

You can do this way also as long as you are maintaining the format of the date.

Warm Regards,

Nikhil Thampi