topic Issue with Using While Loop with Sql in Talend Studio

Issue with Using While Loop with Sql

Karuetl — Wed, 24 Jan 2018 19:30:34 GMT

I have an issue after implementing tLoop , it is not looping dates starting from 1981-02-21 until 1981-02-26 ..

It is starting from 1981-02-22..

Kindly help me out

Gave values in the context as

vStartDate = 1981-02-21

vEndDate = 1981-02-26

tJava_1 as below code

java.util.Date start_date=TalendDate.parseDate("yyyy-MM-dd", context.vStartDate);
java.util.Date end_date=TalendDate.parseDate("yyyy-MM-dd", context.vEndDate);
long l=TalendDate.diffDate(end_date, start_date);
context.vDiff=l;
System.out.println(context.vDiff);
System.out.println(start_date);

tLoop gave as while loop as below

context.vDiff>0L

In the DB sql query as below

"SELECT emp.emp_no,
		emp.emp_name,
		emp.job_title,
		emp.manager_id,
		emp.hire_date,
		emp.salary,
		emp.commission,
		emp.dept_no
FROM	tempdw.emp
where emp.hire_date ='"+context.vStartDate+"'"

Result as below

Re: Issue with Using While Loop with Sql

TRF — Wed, 24 Jan 2018 22:06:21 GMT

Hi,
Connect 1 or more t<yourDb>Row component after a tLoop.
For MySQL, you may have multiple queries for a single tMySqlRow (see this post https://community.talend.com/t5/Migration-Configuration-and/Executing-multiple-SQL-statements-with-one-tMysqlRow-component/ta-p/21672).

Re: Issue with Using While Loop with Sql

Karuetl — Wed, 24 Jan 2018 22:13:54 GMT

I would like to know how i can increment the startdate value and pass it to both loop and th equery

Re: Issue with Using While Loop with Sql

TRF — Thu, 25 Jan 2018 11:04:09 GMT

Refer to TalendDate routines

Re: Issue with Using While Loop with Sql

Karuetl — Thu, 25 Jan 2018 21:39:06 GMT

I have an issue after implementing tLoop , it is not looping dates starting from 1981-02-21 until 1981-02-26 ..

It is starting from 1981-02-22..

Kindly help me out

Gave values in the context as

vStartDate = 1981-02-21

vEndDate = 1981-02-26

tJava_1 as below code

java.util.Date start_date=TalendDate.parseDate("yyyy-MM-dd", context.vStartDate);
java.util.Date end_date=TalendDate.parseDate("yyyy-MM-dd", context.vEndDate);
long l=TalendDate.diffDate(end_date, start_date);
context.vDiff=l;
System.out.println(context.vDiff);
System.out.println(start_date);

tLoop gave as while loop as below

context.vDiff>0L

In the DB sql query as below

"SELECT emp.emp_no,
		emp.emp_name,
		emp.job_title,
		emp.manager_id,
		emp.hire_date,
		emp.salary,
		emp.commission,
		emp.dept_no
FROM	tempdw.emp
where emp.hire_date ='"+context.vStartDate+"'"

Result as below

Re: Issue with Using While Loop with Sql

Anonymous — Fri, 26 Jan 2018 13:37:10 GMT

Hi Karuetl

I think you don't configure the while condition correctly on tLoop. I have a similar case that does a loop on each month, see

context.monthStarting is a variable with int type.

I hope it gives you hints to do a loop on day interval.

Regards

Shong

Re: Issue with Using While Loop with Sql

Karuetl — Fri, 26 Jan 2018 14:11:20 GMT

issue is in the SQL query i am using the context.vStartDate value and for first time it is not passing the start value but the incremental one ...

Re: Issue with Using While Loop with Sql

Karuetl — Fri, 26 Jan 2018 22:08:54 GMT

I am actually incrementing using the tJava2 as below .... Still unable to get first vStartDate into the Query

Re: Issue with Using While Loop with Sql

Anonymous — Sat, 27 Jan 2018 06:54:00 GMT

Hi ,

Can u please attach the screenshot of tloop component section ?

Thanks ,

Praveen M.

Re: Issue with Using While Loop with Sql

TRF — Sat, 27 Jan 2018 13:33:03 GMT

Hi,
"tJava will be executed first, but only once in the subjob" as explained here https://community.talend.com/t5/Design-and-Development/What-is-the-difference-between-tJava-tJavaRow-and-tJavaFlex/ta-p/21505
So, as it seems startDate is incremented in your tJava, the loop starts with 1981-02-22.
Hope this helps.

Re: Issue with Using While Loop with Sql

Karuetl — Mon, 29 Jan 2018 21:47:31 GMT

I have resolved the issue instead of OnSubJob OK , I used onComponent Ok .. It worked well ....