Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Karuetl
Creator II
Creator II

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

 

0683p000009Ls6n.png

 

 

 

 

 

 

 

 

 

Result as below 

0683p000009Lrte.png 

Labels (3)
1 Solution

Accepted Solutions
Karuetl
Creator II
Creator II
Author

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

View solution in original post

10 Replies
TRF
Champion II
Champion II

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...).
Karuetl
Creator II
Creator II
Author

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

TRF
Champion II
Champion II

Karuetl
Creator II
Creator II
Author

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

 

0683p000009Ls6n.png

 

 

 

 

 

 

 

 

 

Result as below 

0683p000009Lrte.png 

Anonymous
Not applicable

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

0683p000009Lroe.png

context.monthStarting is a variable with int type. 

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

 

Regards

Shong

Karuetl
Creator II
Creator II
Author

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

Karuetl
Creator II
Creator II
Author

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

 

0683p000009Ls8K.png

 

 

Anonymous
Not applicable

Hi ,

Can u please attach the screenshot of tloop component section ?

Thanks ,

Praveen M.

TRF
Champion II
Champion II

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.