how to implement a loop function
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
how to implement a loop function
Hi,all
a store procedure after selecting some records then
variable1=0
variable2=0
while (variable1<15) loop
while (variable2<10) loop
sum(amt) where varibale1= field1 and variable2= field2
variable2:= variable2+1
end loop
variable1:=variable1+1
end loop
how to implement this via DS job
a store procedure after selecting some records then
variable1=0
variable2=0
while (variable1<15) loop
while (variable2<10) loop
sum(amt) where varibale1= field1 and variable2= field2
variable2:= variable2+1
end loop
variable1:=variable1+1
end loop
how to implement this via DS job
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Each row selected is processed separately by DataStage. The only way you can create a loop is in a routine. But do you really need to? Is not the loop based upon the rows you are processing? In that case, all you need to do is to initialize and derive an appropriate set of stage variables in a Transformer stage; the "loop" is automatic.
The derivations, it appears to me, would involve If..Then..Else expressions.
The derivations, it appears to me, would involve If..Then..Else expressions.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
a table named temp for information of people in db, and the sp is :
v_type ='Stu'
v_col_number := 1;
v_line := 1;
v_linecol5_total := 0;
v_col1_total := 0;
v_col2_total := 0;
v_col3_total := 0;
v_col4_total := 0;
v_col5_total := 0;
v_line_buf := '';
v_mat_days := 0;
v_n1 := 'N';
v_n2 := 'N';
v_n3 := 'N';
WHILE (v_line < 16)
LOOP
v_err_num := 59;
WHILE (v_col_number < 5)
LOOP
v_err_num := 62;
BEGIN
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
EXCEPTION WHEN no_data_found then
v_print_amt := 0;
v_mat_days := 0;
END;
-- keep running line totals
v_linecol5_total := v_linecol5_total + v_print_amt;
v_col5_total := v_col5_total + v_print_amt;
v_err_num := 65;
IF v_col_number = 1 THEN
v_line_buf := nvl(v_print_amt, '0') || c_cm;
ELSE
v_line_buf := v_line_buf || nvl(v_print_amt, '0') || c_cm;
END IF;
-- keep running column totals
v_err_num := 68;
IF v_col_number = 1 THEN
v_col1_total := v_col1_total + v_print_amt;
ELSE
IF v_col_number = 2 THEN
v_col2_total := v_col2_total + v_print_amt;
ELSE
IF v_col_number = 3 THEN
v_col3_total := v_col3_total + v_print_amt;
ELSE
v_col4_total := v_col4_total + v_print_amt;
END IF;
END IF;
END IF;
v_col_number := v_col_number + 1;
END LOOP;
IF v_type = 'Leader' THEN
v_line_buf := v_line_buf || nvl(v_linecol5_total, '0');
ELSE
v_line_buf := v_line_buf ||c_end;
END IF;
-- print each line
v_err_num := 71;
utl_file.put_line(v_outfile, v_line_buf);
v_col_number := 1;
v_linecol5_total := 0;
v_line := v_line + 1;
END LOOP;
-- print line 16 for each type of person
v_err_num := 74;
v_line_buf := ' ';
v_line_buf := nvl(v_col1_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col2_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col3_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col4_total, '0') || c_cm;
IF v_type = 'Leader' THEN
v_line_buf := v_line_buf || nvl(v_col5_total, '0');
ELSE
v_line_buf := v_line_buf ||c_end;
END IF;
utl_file.put_line(v_outfile, v_line_buf);
please help!
v_type ='Stu'
v_col_number := 1;
v_line := 1;
v_linecol5_total := 0;
v_col1_total := 0;
v_col2_total := 0;
v_col3_total := 0;
v_col4_total := 0;
v_col5_total := 0;
v_line_buf := '';
v_mat_days := 0;
v_n1 := 'N';
v_n2 := 'N';
v_n3 := 'N';
WHILE (v_line < 16)
LOOP
v_err_num := 59;
WHILE (v_col_number < 5)
LOOP
v_err_num := 62;
BEGIN
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
EXCEPTION WHEN no_data_found then
v_print_amt := 0;
v_mat_days := 0;
END;
-- keep running line totals
v_linecol5_total := v_linecol5_total + v_print_amt;
v_col5_total := v_col5_total + v_print_amt;
v_err_num := 65;
IF v_col_number = 1 THEN
v_line_buf := nvl(v_print_amt, '0') || c_cm;
ELSE
v_line_buf := v_line_buf || nvl(v_print_amt, '0') || c_cm;
END IF;
-- keep running column totals
v_err_num := 68;
IF v_col_number = 1 THEN
v_col1_total := v_col1_total + v_print_amt;
ELSE
IF v_col_number = 2 THEN
v_col2_total := v_col2_total + v_print_amt;
ELSE
IF v_col_number = 3 THEN
v_col3_total := v_col3_total + v_print_amt;
ELSE
v_col4_total := v_col4_total + v_print_amt;
END IF;
END IF;
END IF;
v_col_number := v_col_number + 1;
END LOOP;
IF v_type = 'Leader' THEN
v_line_buf := v_line_buf || nvl(v_linecol5_total, '0');
ELSE
v_line_buf := v_line_buf ||c_end;
END IF;
-- print each line
v_err_num := 71;
utl_file.put_line(v_outfile, v_line_buf);
v_col_number := 1;
v_linecol5_total := 0;
v_line := v_line + 1;
END LOOP;
-- print line 16 for each type of person
v_err_num := 74;
v_line_buf := ' ';
v_line_buf := nvl(v_col1_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col2_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col3_total, '0') || c_cm;
v_line_buf := v_line_buf || nvl(v_col4_total, '0') || c_cm;
IF v_type = 'Leader' THEN
v_line_buf := v_line_buf || nvl(v_col5_total, '0');
ELSE
v_line_buf := v_line_buf ||c_end;
END IF;
utl_file.put_line(v_outfile, v_line_buf);
please help!
Last edited by jenny_wang on Tue Dec 18, 2007 1:40 am, edited 2 times in total.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Keep running totals and other derived values in stage variables.
Detect group change either in stage variables or (easier) in Sort stage (key change column or cluster key change column).
Use detected change to constrain output.
Detect group change either in stage variables or (easier) in Sort stage (key change column or cluster key change column).
Use detected change to constrain output.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 7
- Joined: Mon Dec 17, 2007 1:37 am
- Location: Boston, MA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 7
- Joined: Mon Dec 17, 2007 1:37 am
- Location: Boston, MA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The first job in a sequence runs, and processes zero or more rows. In a routine activity you determine, probably via a call to DSGetLinkInfo(), whether the row count was zero or otherwise. A custom trigger on the routine activity then determines whether the second job, which generates the "all zeroes" row, runs.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I'm afraid I don't understand this latest question in the context of the earlier posts in this thread. What temp? What is Stu? What is the significance of line 4?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
I mean if in the early stage i only get the data whose type is Stu, and line is 4, so i can calculate the number only for Stu which line is 4, but the number for Stu whose line<4 should be 3 lines ,all are 0,0,0,0
i don't know how to generate the 3 lines and output before the number which i calculate for Stu whose line =4
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
i don't know how to generate the 3 lines and output before the number which i calculate for Stu whose line =4
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
I mean if in the early stage i only get the data whose type is Stu, and line is 4, so i can calculate the number only for Stu which line is 4, but the number for Stu whose line<4 should be 3 lines ,all are 0,0,0,0
i don't know how to generate the 3 lines and output before the number which i calculate for Stu whose line =4
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
i don't know how to generate the 3 lines and output before the number which i calculate for Stu whose line =4
SELECT nvl(round(sum(amt)/1000000), '0')
into v_print_amt
from temp
where type = v_type
and col_num = v_col_number
and line = v_line;
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Derive each of the output columns as something like
I am assuming here that Stu is a field name, even though you used the word "type".
Code: Select all
If @INROWNUM = 4 Then Stu Else 0
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 26
- Joined: Mon Nov 19, 2007 2:55 am
- Location: Hangzhou
the data sorted after selected from db is :
Type line col amt
Stu 1 2 100
Stu 1 2 110
Stu 1 4 200
Stu 4 1 999
Stu 4 3 111
line is 1~15 and col is 1~4
the total amt for col 2 when line =1 is 210 and the total amt for col 4 when line =1 is 200, so the output for line=1 is 0,210,0,200
also the output for line = 4 is 999,0,111,0
but if there is no records whose line = 2 or line =3, how to generate the output 0,0,0,0 in DS?
Type line col amt
Stu 1 2 100
Stu 1 2 110
Stu 1 4 200
Stu 4 1 999
Stu 4 3 111
line is 1~15 and col is 1~4
the total amt for col 2 when line =1 is 210 and the total amt for col 4 when line =1 is 200, so the output for line=1 is 0,210,0,200
also the output for line = 4 is 999,0,111,0
but if there is no records whose line = 2 or line =3, how to generate the output 0,0,0,0 in DS?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Finally I understand. You want to generate rows when there are no source rows, so that you have an unbroken sequence of integers in the column. Probably the easiest way is to generate a sequence of integers (Row Generator stage) in a separate stream and outer join that to your existing stream. Convert the nulls (caused by line not found) to zeroes.
Code: Select all
line
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.