Execute SELECT WHERE id IN(...) programmatically in Load Script

[virilo_tejedor]

I'd like to interate over a list of M ids to exectue a select M/N times using a batch size of N:

Pseudocode:

[IDS_LIST]:
   LOAD [id]
FROM [lib://POC/today_million_ids_list.csv]

LIB CONNECT TO 'MY_ORACLE_DB';

[TABLE]
for i in M step N:
   SELECT * FROM my_table WHERE table_id in (id[i+1], id[i+2]... id[i+N])

Is it possible to do from a load script?

Thanks in advance

[virilo_tejedor · Accepted solution]

I solved it with a little bit of this and a little bit of that.

There must be a smarter solution, but it worked for me:

trace 'Batches';

LET vNumRows = NoOfRows('TABLE_IN_CSV'); // There musn't be duplicates in this table in order FieldValue not to return some extra 'nulls'
LET vBatchSize=30;
  

for i=1 to $(vNumRows) step vBatchSize // loop through every row
    trace 'Loading $(i) of $(vNumRows) rows.';
    let vWhereInClause = FieldValue('TABLE_ID',$(i)); //get the value for "text" field on each row
    
    vMinJ=$(i)+1
    
    //vMaxJ=Min($(vNumRows), $(i)+$(vBatchSize)-1)
    vMaxJ=if($(vNumRows)< $(i)+$(vBatchSize)-1, $(vNumRows),$(i)+$(vBatchSize)-1)
    
    for j=$(vMinJ) to $(vMaxJ)
     vWhereInClause='$(vWhereInClause)' & ',' & FieldValue('TABLE_ID',$(j))
    next
    
    //trace 'vWhereInClause $(vWhereInClause)';

[MY_ORACLE_TABLE]:
LOAD 
  ...
;SELECT
  *
FROM MY_ORACLE_TABLE
    WHERE TABLE_ID in ($(vWhereInClause))
;


next