CODEWITHSUNDEEP
oracle-databaseif-statementstored-proceduresplsql
user206168
user206168

Reputation: 1025

Check if Exists PLS-00405: subquery not allowed in this context

I have cursor it selects from TableA then Fetch Loop that inserts into TableB.

I want to check if the value already exists in the TableB.

If it exists then I want to skip the insert.

create or replace
PROCEDURE DAILY_RPT (
    v_start       IN DATE,
    v_end        IN DATE)

IS

    ao_out_no   out_pair.out_no%type;

cursor get is
SELECT ao_out_no from tableA;

BEGIN
 open get;
     LOOP
     fetch get into ao_out_no;
      EXIT WHEN get%NOTFOUND;
     if (ao_out_no = (select out_no from TableA where out_no = ao_out_no) THEN
     --DO NOTHING
     else 
        INSERT INTO TABLEB(OUT_NO) VALUES (ao_out_no);
     end if;

        END LOOP;
     close get;

END;

I used IF CONDITION however, I used variable into if condition & I am getting below.

PLS-00405: subquery not allowed in this context

if (ao_out_no = (select out_no from TableA where out_no = ao_out_no) THEN

Upvotes: 3

Views: 8834

Answers (3)

William Robertson
William Robertson

Reputation: 16001

With corrected syntax, it would be something like this:

create or replace procedure daily_rpt
    ( v_start in date
    , v_end   in date )
as
begin
    for r in (
        select ao_out_no, 0 as exists_check
        from   tablea
    )
    loop
        select count(*) into exists_check
        from   tablea
        where  out_no = r.ao_out_no
        and    rownum = 1;

        if r.exists_check > 0 then
            --DO NOTHING
        else
            insert into tableb (out_no) values (r.ao_out_no);
        end if;

    end loop;
end;

However, it's inefficient to query all of the rows and then do an additional lookup for each row to decide whether you want to use it, as SQL can do that kind of thing for you. So version 2 might be something like:

create or replace procedure daily_rpt
    ( v_start in date
    , v_end   in date )
as
begin
    for r in (
        select ao_out_no
        from   tablea
        where  not exists
               ( select count(*)
                 from   tablea
                 where  out_no = r.ao_out_no
                 and    rownum = 1 )
    )
    loop
        insert into tableb (out_no) values (r.ao_out_no);
    end loop;
end;

at which point you might replace the whole loop with an insert ... where not exists (...) statement.

Upvotes: 1

coco
coco

Reputation: 134

Use the following :

for i in (
    select out_no from TableA where out_no
)
loop
    if i.out_no = ao_out_no
    then
        -- DO NOTHING
    else 
        ...

or create a new variable named x, and then assign a value to it by 

select out_no into x from TableA where out_no = ao_out_no;

and check returning value for x.

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175826

You don't need cursor or PL/SQL at all:

INSERT INTO TABLEB(OUT_NO) 
SELECT ao_out_no 
FROM tableA ta
WHERE ... -- filtering rows
  AND NOT EXISTS (SELECT * From TableB tb WHERE tb.OUT_NO = ta.ao_out_no);

Upvotes: 1

Related Questions