how to implement a loop function

[jenny_wang]

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

[ray.wurlod]

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.

[jenny_wang]

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!

[ray.wurlod]

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.

[gmt_etldev]

one question:
what if there is no records come from the last stage, how to generate the 0,0,0,0,0 line

[ray.wurlod]

Most easily in a separate job, run after the first, that checks the link row count in the first job and generates the zeroes row conditionally.

[gmt_etldev]

could you please give more details?

[ray.wurlod]

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.

[jenny_wang]

Thanks

if in the temp there is only Stu information whose line number is 4, and the all zero's lines should be gerenated by a routine?

[ray.wurlod]

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?

[jenny_wang]

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;

[jenny_wang]

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;

[ray.wurlod]

Derive each of the output columns as something like

Code: Select all

If @INROWNUM = 4 Then Stu Else 0

I am assuming here that Stu is a field name, even though you used the word "type".

[jenny_wang]

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?

[ray.wurlod]

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

Code: Select all

line 

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.