Populate column in block using values in another column [message #425638] |
Sun, 11 October 2009 02:11 |
adnanBIH
Messages: 41 Registered: November 2007 Location: BiH, Sarajevo
|
Member |
|
|
Hello everyone. I want to populate second column by passing through every row in first column (row by row).
Procedure fires in WHEN-NEW-BLOCK-INSTANCE trigger. I marked with red color part of code which is suspicious.Here's the code:
PROCEDURE getanalit_from_old_io_plan
IS
CURSOR fill
IS
SELECT DISTINCT SUM (DECODE (SIGN (i_gotr - god), 1, i_goizn, 0))
FROM plan_ul_op, acc_orgdio, v_invodl, rn_invtru_jp
WHERE siforg = i_orgj
AND sifra = siforg
AND i_pbo = radn(+)
AND sifra = :parameter.sifra
AND i_gotr > god
AND i_pbo = :i_pbo --> suspicious line
GROUP BY i_pbo;
BEGIN
FIRST_RECORD;
OPEN fill;
FETCH fill
INTO :i_goizn2;
LOOP
IF fill%FOUND
THEN
NEXT_RECORD;
FETCH fill
INTO :i_goizn2;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE fill;
END;
[Code reformatted by LF. Removed link to a non-existent image. Removed COLOR formatting as it doesn't work within [code] tags.]
[Updated on: Sun, 11 October 2009 04:09] by Moderator Report message to a moderator
|
|
|
Re: Populate column in block using values in another column [message #425651 is a reply to message #425638] |
Sun, 11 October 2009 04:27 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Why DISTINCT with a GROUP BY?
Why cursor?
Why such an unusual loop? I don't think I've ever seen anything like that.
As far as I can tell, cursor loop is not the way to do that. What you might need is a loop through all records in a block (but you'd do that by looping until :SYSTEM.LAST_RECORD is TRUE). Then SELECT the value you've calculated into an item. Something like loop
select ...
into :an_item
from ...
exit when :system.last_record = 'TRUE';
next_record;
end loop;
|
|
|