Where ussing lookup in arry

[Thunder]

How can i do this in sqlrpgle?


D array1 s 20 dim(50)

select FIELDA from File1 where FIELDB|| FIELDC not in (Select * from ARRAY1)

[Vectorspace]

I don't think you can, I'm afraid. (Hopefully someone will prove me wrong)

I can think of two workarounds

1, create a temporary table, insert the array, and join with it:

exec sql create table qtemp/temp1 (
  val  numeric(20)
);

x = number of populated entries in array1

exec sql insert into qtemp/temp1
  :x rows values(array1:);

exec sql select FIELDA from File1
  where FIELDB||FIELDC not in (select val from qtemp/temp1);

Caveat - I'm not sure what FIELDB||FIELDC is supposed to do? It seems like you are concatenating two string fields, but your array contains numeric values?

2, build your SQL as a string executed with prepare/execute or execute immediate

dcl-s sql varchar(1000) inz('');

sql = 'select FIELDA from File1
  where FIELDB||FIELDC not in ('

x = number of populated entries in array1

for i = 1 to x by 1;
  if i > 1;
    sql += ','
  endIf;
  sql += array1(i)
endFor;

sql += ')';

exec sql execute immediate :sql;

I think you might be able to streamline the for loop using %join() if your OS version is high enough (ours is not so haven't used join myself yet)


I'm not sure of the benefits of each method, or if there is a better way