RPG Embedded SQL delete several records with a list

Question

How can I delete specific records from a table? I have a list with objects which I have to delete.

I've tried to save these names in a variable withh seperators and compare the key column with these variable.

PGNALIST = '(''PGM1'',''PGM2'',''PGM3'',...)'; 

EXEC SQL DELETE FROM FILE WHERE FILEPGNA IN :PGNALIST;

But it only works if I have a variable with just ONE program name.

Do know how I can solve these problem?

Also need these for SQL updates... The goal ist to use embedded Sql further.

Thanks a lot

Answer 1

Can't do it like that...

For static SQL, you'd have to have multiple variables:

EXEC SQL 
  DELETE FROM FILE 
  WHERE FILEPGNA IN (:p1, :p2, :p3, ...);

Of course you need to know how many, or at least the maximum number of values you'll need to pass in.

Alternatively, you can use dynamic SQL to build the statement at runtime..

   dcl-c QUOTE '''';
   dcl-s myStmt varchar(1000);

   myStmt = 'delete from file where filepgna in ('
              + QUOTE + 'PGM1' + QUOTE
              + QUOTE + 'PGM2' + QUOTE
              + QUOTE + 'PGM3' + QUOTE 
              + ')';
   exec sql
    execute immediate :myStmt;

note that the QUOTE constant just makes life a little easier.