Newbie help 1

[dscoiho]

I am trying to get the following to compare partnums and update the Quanhand with the ordquan. Any suggestions would be helpful.


drop procedure P1;
create procedure P1 is
ws_PARTNUM NUMBER(10);
wsmif_PARTNUM NUMBER(10);
ws_ORDQUAN NUMBER(5);
ws_QUANHAND NUMBER(4);
ws_QOH NUMBER(4);

cursor CURMOF is select MOF_PARTNUM,MOF_ORDQUAN from MOF3C;
cursor CURMIF is select MIF_PARTNUM,MIF_QUANHAND from MIF3C;
begin
open CURMOF;
open CURMIF;
Loop
fetch CURMOF into ws_PARTNUM,ws_ORDQUAN;
fetch CURMIF into wsmif_PARTNUM,ws_QUANHAND;
exit when CURMOF%notfound;
if ws_PartNUM = wsmif_Partnum then
UPDATE MIF3C
Set MIF_QUANHAND =( WS_QUANHAND-WS_ORDQUAN);
end if;

end loop;

Close CURMOF;
CLose CURMIF;

end P1;
/

 

[SantaMufasa]

DSCoiho,

The following code seems a bit tighter, yet does the same thing:

drop procedure P1;
create procedure P1 is
begin
    for x in (select MOF_PARTNUM
                    ,MOF_ORDQUAN
                    ,MIF_PARTNUM
                    ,MIF_QUANHAND
                from MOF3C,MIF3C
               where MOF_PARTNUM = MIF_PARTNUM) loop
        update MIF3C set MIF_QUANHAND =( WS_QUANHAND-WS_ORDQUAN)
         where MIF_PARTNUM = x.MIF_PARTNUM;
    end loop;
    commit;
end P1;
/

Let us know your reactions to this code.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[SantaMufasa]

Sorry, DSCoiho, this is what happens when I don't have a sample set of data to check against...My code above should have read:

...update MIF3C set MIF_QUANHAND =([b]x.MIF_QUANHAND-x.MOF_ORDQUAN[/b])...

Sorry.

Let us know how this works.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]