Subquery in SQL with LOOP

Question

I am getting an error when trying to run a select statement in a PLSQL loop. How can I get it to work?

DECLARE 
    v_gsp_cnt number := 0;
BEGIN
    WHILE v_gsp_cnt > (select max(eachesrem) from salesplan)
    LOOP
        insert into salesplanexp (select SPNUM,ORDNUM,DMDTYPE,ORDSTATUS,ITEM,LOC,ITEMDESCR,FCST,ORDTYPE,DELSERVLVL,SHIPMETHOD,CARRIER,EACHES,SHIPPEDEACHES,EACHESREM,WEIGHT,VOLUME,STARTSHIPDATE,ENDSHIPDATE,ACTSHIPDATE,STARTDELDATE,ENDDELDATE,ACTDELDATE,ORDCREATEDATE,ORDCONFDATE,BILLCUSTNAME,BILLADD1,BILLADD2,BILLCITY,BILLSTATE,BILLCOUNTRY,BILLZIP,SHIPCUSTNAME,SHIPADD1,SHIPADD2,SHIPCITY,SHIPSTATE,SHIPCOUNTRY,SHIPZIP,LOCPHONE,LOCEMAIL,ACTITEMCOST,ACTITEMPRICE,ACTITEMMARGIN,ACTSHIPCOST,ACTSHIPPRICE,ACTSHIPMARGIN,ACTTOTCOST,ACTTOTPRICE,ACTTOTMARGIN,BRAND,CHANNEL,EXTORDID,EXTLINEID,SOURCESYS,CREATEDATE,LASTUPDATEDDATE from salesplan);
        commit;
        select x.new_cnt into v_gsp_cnt from (select v_gsp_cnt+1 from dual) x; 
        commit;
    END LOOP;
END;

The error is

ORA-06550: line 4, column 23:
PLS-00405: subquery not allowed in this context
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

The error, I believe, is from the select statement. But with the loop, I'm not sure another way to make it run properly

An error you say. Would you mind sharing it so we can help you without having to guess? — JNevill
– JNevill, Commented Apr 16, 2018 at 20:12
Perhaps: select x.new_cnt into v_gsp_cnt from (select v_gsp_cnt+1 as new_cnt from dual) x; ??? — JNevill
– JNevill, Commented Apr 16, 2018 at 20:13
By the way, it is better to commit once at the end rather than multiple times per loop iteration. For one thing, if it fails partway through you could restart cleanly. — William Robertson
– William Robertson, Commented Apr 17, 2018 at 16:03

Ditto · Accepted Answer · 2018-04-16 20:24:24Z

3

The error is coming from this line:

WHILE v_gsp_cnt > (select max(eachesrem) from salesplan)

the actual error is a bit deceiving, but that's the source (note it says "line 4" .. count from DECLARE).

You can't have a query like this in this place.

try this instead:

select max(eachesrem) into v_max from salesplan;

WHILE v_gsp_cnt > v_max

(and as well mentioned by JNevill: you need to alias the inner "v_gsp_cnt+1" as new_cnt ... ) but that's a different error which will show up after you fix the one above. ;)

answered Apr 16, 2018 at 20:24

Ditto

3,3693 gold badges16 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

baskinsr Over a year ago

I got the error that v_max must be declared. So I attempted to declare as v_max :=(select max(eachesrem) from salesplan; and received an error on that as well

baskinsr Over a year ago

I found my error on that. This worked perfectly. Thanks!

Ditto Over a year ago

@baskinsr: typically you declare a variable not by assigning it a value, but by actually "declaring" it ... ie: v_max NUMBER;

Collectives™ on Stack Overflow

Subquery in SQL with LOOP

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related