CODEWITHSUNDEEP
plsql
Amir
Amir

Reputation: 113

Error in PL/SQL statement.....not giving me the output

I am updating two tables (card) and (accountcomment).

First query:

     update card set cardstatusid = 3 where cardstatusid = 1 and card num in ('123', '456', '789');'

Second Query:

           Update accountcomment set text = 'CARDS EXPIRED' WHERE commentcategory = 10 and accountid in ('aaa', 'bbb', 'ccc');'

Since there are bunch of cards and account numbers, I need to declare a cursor and use a PL/SQL statement.

So I wrote the following:

    declare
    cursor c1 is select personid from card where cardstatusid = 1 and card 
    num in ('123', '456', '789');

     cursor c2 is select accountid from personaccount where personid in 
     (select personid from card where cardstatusid = 1 and card num in 
     ('123', '456', '789'));

      begin

      for r1 in c1
      loop
      update card set cardstatusid = 3
      where personid = r1.personid;
      end loop;

      for r2 in c2
      insert into accountcomment 
      values ('CARDS EXPIRED', r2.accountid, 10);
      end loop;

      end;

It is giving me an error.....I am not sure if I am using the cursors wrong or some statement......and I cant use an update statement in accountcomment because commentcategory 10 doesnt exits yet in that table.

If I can use one cursor that would be great. otherwise help is appreciated in fixing the current pl/sql.

Thanks!

Upvotes: 0

Views: 58

Answers (1)

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

card num is not a valid column_name (space). Also you are missing a "loop".

Something like this?

declare
   cursor c1 is
      select personid
        from card
       where cardstatusid = 1 and **cardnum** in ('123', '456', '789');

   cursor c2 is
      select accountid
        from personaccount
       where personid in (select personid
                            from card
                           where cardstatusid = 1 and **cardnum** in ('123', '456', '789'));
begin
   for r1 in c1 loop
      update card
         set cardstatusid = 3
       where personid = r1.personid;
   end loop;

   for r2 in c2 **loop**
      insert into accountcomment
           values ('CARDS EXPIRED', r2.accountid, 10);
   end loop;
end;

Upvotes: 1

Related Questions