Announcement

Collapse
No announcement yet.

Where ussing lookup in arry

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Where ussing lookup in arry

    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)

  • #2
    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:
    Code:
    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
    Code:
    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

    Comment

    Working...
    X