newbie help again 2

[dscoiho]

OK didnt realize that I need this to be sequential. So below I have attempted to do that but when I run the numbers I get -1 on all my MIF_ORDQUAN no matter what MIF_PARTNUM. If anyone out there could point out what could be wrong with the Procedure.

----------------------------------
DSCMOF3c table

MOF_ORDERNUM char(10),
MOF_CUSTNUM char(8),
MOF_DATE date,
MOF_PARTNUM numeric(10),
MOF_ORDQUAN numeric(5));

----------------------------

DSCMIF3c Table

MIF_PARTNUM numeric(10),
MIF_DESCRIPTION char(20),
MIF_QUANHAND numeric(4),
MIF_ORDERPT numeric(4),
MIF_ORDQUAN numeric(4),
MIF_PRICE numeric(8,2),
MIF_COST numeric(8,2),
MIF_CLASS char(1));

---------------------------
drop procedure DSCLab3c;
create procedure DSCLab3c is

ws_ORDERNUM CHAR(10);
ws_PARTNUM NUMBER(10);
ws_ORDQUAN NUMBER(5);
WS_QOH NUMBER(5);
WS_MIFPARTNUM NUMBER(10);

cursor MOFCUR is
select MOF_ORDERNUM, MOF_PARTNUM, MOF_ORDQUAN from DSCMOF3C
order by MOF_PARTNUM;
cursor MIFCUR is
select MIF_QUANHAND,MIF_PARTNUM from DSCMIF3C
order by MIF_PARTNUM;

BEGIN
OPEN MOFCUR;
OPEN MIFCUR;

LOOP
FETCH MIFCUR INTO WS_QOH,WS_MIFPARTNUM;

IF MIFCUR%NOTFOUND THEN
null;
goto eofmif;
END IF;
LOOP
Fetch MOFCUR INTO ws_ORDERNUM,ws_PARTNUM,ws_ORDQUAN;
IF MOFCUR%NOTFOUND THEN
NULL;
EXIT;
ELSE
if WS_MIFPARTNUM = WS_PARTNUM THEN
update DSCMIF3C set DSCMIF3C.MIF_QUANHAND = (WS_QOH - ws_ORDQUAN)
where WS_MIFPARTNUM = ws_PARTNUM;
END IF;
END IF;
END LOOP;
END LOOP;

close MOFCUR;
CLOSE MIFCUR;

<<EOFMIF>>
CLOSE MIFCUR;


end DSCLab3c;
/

 

[SantaMufasa]

dscoiho, I'll be happy to help (and even optimise your code) if you can please post some code here for me/us:

CREATE TABLE DSCMOF3C...(just the columns appearing in your proc)
CREATE TABLE DSCMIF3C...(just the columns appearing in your proc)

INSERT INTO DSCMOF3C...a few representative rows
INSERT INTO DSCMOF3C...a few representative rows

If you can post this, then we can more effectively troubleshoot your problem. I'll also post much tighter code for you.

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]

 

[dscoiho]

Create Table DSCMIF3C(
MIF_PARTNUM numeric(10),
MIF_DESCRIPTION char(20),
MIF_QUANHAND numeric(4),
MIF_ORDERPT numeric(4),
MIF_ORDQUAN numeric(4),
MIF_PRICE numeric(8,2),
MIF_COST numeric(8,2),
MIF_CLASS char(1));
desc DSCMIF3C;
insert into DSCMIF3C values(10000000,'Girl Doll',3,2,10,35,20,2);
insert into DSCMIF3C values(10000001,'Boy Doll',10,2,10,30,15,2);
insert into DSCMIF3C values(10000002,'Teddy Bear',100,25,50,40,20,2);
insert into DSCMIF3C values(10000003,'Sled',50,2,20,100,40,2);
insert into DSCMIF3C values(10000004,'Computer',25,5,15,500,300,1);
insert into DSCMIF3C values(10000005,'Wagon',4,4,10,75,50,3);
insert into DSCMIF3C values(10000006,'Board Games',50,5,50,12,8,4);
insert into DSCMIF3C values(10000007,'Gold Ring',10,1,10,150,25,1);
insert into DSCMIF3C values(10000008,'Sweater',10,1,10,45,5,4);
insert into DSCMIF3C values(10000009,'Girl Bike',2,4,40,100,60,3);
insert into DSCMIF3C values(10000010,'Boy Bike',5,5,40,100,60,3);
insert into DSCMIF3C values(10000011,'Perfume',9,4,25,25,10,3);
insert into DSCMIF3C values(10000012,'ABC Blocks',6,5,25,15,12,2);
insert into DSCMIF3C values(10000013,'Ice Skates',14,10,20,50,33,3);
insert into DSCMIF3C values(10000014,'Roller Blades',10,8,15,60,36,3);
---------------------------------
Create Table DSCMOF3C(
MOF_ORDERNUM char(10),
MOF_CUSTNUM char(8),
MOF_DATE date,
MOF_PARTNUM numeric(10),
MOF_ORDQUAN numeric(5));
desc DSCMOF3C;
insert into DSCMOF3C values('0000010001','00000100','10-Feb-06',10000010,1);
insert into DSCMOF3C values('0000010002','00000102','05-Jan-06',10000004,2);
insert into DSCMOF3C values('0000010003','00000103','15-Jan-06',10000000,1);
insert into DSCMOF3C values('0000010003','00000103','15-Jan-06',10000012,1);
insert into DSCMOF3C values('0000010004','00000104','06-Jan-06',10000007,2);
insert into DSCMOF3C values('0000010004','00000104','06-Jan-06',10000011,2);
insert into DSCMOF3C values('0000010004','00000104','06-Jan-06',10000002,4);
insert into DSCMOF3C values('0000010005','00000111','15-Feb-06',10000008,2);
insert into DSCMOF3C values('0000010005','00000111','15-Feb-06',10000004,2);
insert into DSCMOF3C values('0000010013','00000105','05-Jan-06',10000003,25);
insert into DSCMOF3C values('0000010013','00000105','05-Jan-06',10000005,25);
insert into DSCMOF3C values('0000010013','00000105','05-Jan-06',10000000,12);
insert into DSCMOF3C values('0000010013','00000105','05-Jan-06',10000001,13);
insert into DSCMOF3C values('0000010013','00000105','05-Jan-06',10000004,25);
insert into DSCMOF3C values('0000010020','00000105','16-Jan-06',10000003,3);
insert into DSCMOF3C values('0000010020','00000105','16-Jan-06',10000005,3);
insert into DSCMOF3C values('0000010020','00000105','16-Jan-06',10000000,4);
insert into DSCMOF3C values('0000010020','00000105','16-Jan-06',10000001,5);
insert into DSCMOF3C values('0000010020','00000105','16-Jan-06',10000004,3);
insert into DSCMOF3C values('0000010014','00000110','08-Jan-06',10000013,1);
insert into DSCMOF3C values('0000010019','00000114','10-Jan-06',10000014,1);
insert into DSCMOF3C values('0000010019','00000114','10-Jan-06',10000005,1);
insert into DSCMOF3C values('0000010019','00000114','10-Jan-06',10000002,1);

 

[SantaMufasa]

So, dscoiho, here is your new procedure (which you could also run as a single SQL UPDATE statement if you prefer)...and it runs lightning fast and produces correct results (from the data that you posted...Thanks for posting all that, BTW !!!):

create or replace procedure DSCLab3c is
BEGIN
    update DSCMIF3C a
       set MIF_QUANHAND = (select a.MIF_QUANHAND-sum(MOF_ORDQUAN)
                             from DSCMOF3C
                            where MOF_PARTNUM = a.MIF_PARTNUM)
     where exists (select 'xxxx' from DSCMOF3C
                    where MOF_PARTNUM = a.MIF_PARTNUM);
end;
/

Procedure created.

SQL> exec DSCLab3c

PL/SQL procedure successfully completed.

MIF_PARTNUM MIF_DESCRIPTION      MIF_QUANHAND MIF_ORDERPT MIF_ORDQUAN  MIF_PRICE   MIF_COST M
----------- -------------------- ------------ ----------- ----------- ---------- ---------- -
   10000000 Girl Doll                     -14           2          10         35         20 2
   10000001 Boy Doll                       -8           2          10         30         15 2
   10000002 Teddy Bear                     95          25          50         40         20 2
   10000003 Sled                           22           2          20        100         40 2
   10000004 Computer                       -7           5          15        500        300 1
   10000005 Wagon                         -25           4          10         75         50 3
   10000006 Board Games                    50           5          50         12          8 4
   10000007 Gold Ring                       8           1          10        150         25 1
   10000008 Sweater                         8           1          10         45          5 4
   10000009 Girl Bike                       2           4          40        100         60 3
   10000010 Boy Bike                        4           5          40        100         60 3
   10000011 Perfume                         7           4          25         25         10 3
   10000012 ABC Blocks                      5           5          25         15         12 2
   10000013 Ice Skates                     13          10          20         50         33 3
   10000014 Roller Blades                   9           8          15         60         36 3

15 rows selected.
*************************************************************************************************

Let us know your thoughts/reactions/likes/dislikes about the above alternative.


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]

 

[dscoiho]

SantaMufasa again thanks for all your help on the script. My question is why would someone want two cursors running through a script in the example I gave?

 

[SantaMufasa]

Dscoiho,

1) There are definitely cases where Parent-Child (Outer cursor-Inner cursor) processing is in order, but this is obviously not one of those cases.

2) We could certainly get your code to work properly, doing what you want.

3) If I were to do your task with PL/SQL, I would, however, opt for implicit cursors instead instead of the explicit cursors that your original code contains. Using implicit cursors, code might look like this:

create or replace procedure DSCLab3c is
    tot_ordquan number;
BEGIN
    for x in (select MIF_QUANHAND,MIF_PARTNUM from DSCMIF3C
        order by MIF_PARTNUM) loop
        tot_ordquan := 0;
        for y in (select MOF_ORDERNUM, MOF_PARTNUM, MOF_ORDQUAN
                    from DSCMOF3C
                   where MOF_PARTNUM = x.MIF_PARTNUM) loop
            tot_ordquan := tot_ordquan + y.mof_ordquan;
        end loop;
        if tot_ordquan > 0 then
            update DSCMIF3C
               set MIF_QUANHAND = MIF_QUANHAND - tot_ordquan
             where MIF_PARTNUM = x.MIF_PARTNUM;
        end if;
    end loop;
end;
/

Procedure created.

SQL> exec DSCLab3c

PL/SQL procedure successfully completed.

SQL> select * from DSCMIF3C;

MIF_PARTNUM MIF_DESCRIPTION      MIF_QUANHAND MIF_ORDERPT MIF_ORDQUAN  MIF_PRICE   MIF_COST M
----------- -------------------- ------------ ----------- ----------- ---------- ---------- -
   10000000 Girl Doll                     -14           2          10         35         20 2
   10000001 Boy Doll                       -8           2          10         30         15 2
   10000002 Teddy Bear                     95          25          50         40         20 2
   10000003 Sled                           22           2          20        100         40 2
   10000004 Computer                       -7           5          15        500        300 1
   10000005 Wagon                         -25           4          10         75         50 3
   10000006 Board Games                    50           5          50         12          8 4
   10000007 Gold Ring                       8           1          10        150         25 1
   10000008 Sweater                         8           1          10         45          5 4
   10000009 Girl Bike                       2           4          40        100         60 3
   10000010 Boy Bike                        4           5          40        100         60 3
   10000011 Perfume                         7           4          25         25         10 3
   10000012 ABC Blocks                      5           5          25         15         12 2
   10000013 Ice Skates                     13          10          20         50         33 3
   10000014 Roller Blades                   9           8          15         60         36 3

15 rows selected.

Items to notice:

1) The results match the correct results from the SQL-only UPDATE statement.

2) An UPDATE statement executes once for each of the many rows in the DSCMIF3C. This is much less efficient than only one execution of the UPDATE statement that I suggested in my earlier SQL-only solution.

3) The use of implicit cursors in a CURSOR FOR LOOP relieves the coder from:

a) separately declaring a cursor in the declare section of the PL/SQL,
b) explicitly OPENing the cursor,
c) explicitly FETCHing data
d) explicitly declaring "INTO" variables,
e) explicitly checking for %NOTFOUND condition, then redirecing processing if TRUE,
f) explicitly CLOSEing the cursor.

Cool, huh?

Let us know if this answers your questions.

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]

 

[dscoiho]

Thanks that makes more sense now. I appreciate all your help on this. As always this website is helpful into getting answers to even some of most simpliest problems.

 

[Welshbird]

dscoiho,

I'd just suggest that it might be nice to award Santa with a star for his help.

Thanks,



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SantaMufasa]

Thank you, Fee, for your thoughtfulness.

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]

 

[dscoiho]

SantaMufasa just let you know I did send the star to you plus wanted to give you a personal thank you also.

 

[dscoiho]

Sorry didnt realize I didnt click on confirm. My apologizes.

 

[SantaMufasa]

And Thank You, DSCoiho, for your thoughtfulness, as well.

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]