Issue with Using While Loop with Sql

Six Stars

Issue with Using While Loop with Sql

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+"'"

 

1.PNG

 

 

 

 

 

 

 

 

 

Result as below 

2.PNG 


Accepted Solutions
Six Stars

Re: Issue with While Loop with SQL

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


All Replies
Forteen Stars TRF
Forteen Stars

Re: Using While Loop with SqlRequest

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-o...).

TRF
Six Stars

Re: Using While Loop with SqlRequest

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

Forteen Stars TRF
Forteen Stars

Re: Using While Loop with SqlRequest

Refer to TalendDate routines


TRF
Six Stars

Re: Issue with While Loop with SQL

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+"'"

 

1.PNG

 

 

 

 

 

 

 

 

 

Result as below 

2.PNG 

Community Manager

Re: Issue with While Loop with SQL

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

1.png

context.monthStarting is a variable with int type. 

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

 

Regards

Shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: Issue with While Loop with SQL

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 ...

Six Stars

Re: Issue with While Loop with SQL

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

 

1.PNG

 

 

Six Stars

Re: Issue with While Loop with SQL

Hi ,

Can u please attach the screenshot of tloop component section ?

Thanks ,

Praveen M.

Forteen Stars TRF
Forteen Stars

Re: Issue with While Loop with SQL

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...
So, as it seems startDate is incremented in your tJava, the loop starts with 1981-02-22.
Hope this helps.

TRF
Six Stars

Re: Issue with While Loop with SQL

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