Using a looped variable in SQL load where clause?

[AyCe1082]

Hi, is it possible when doing an SQL load to add a where clause that has values in an Oracle DB Column not equal to $(variable) ?

The variable will loop through a list of IDs so the resultant table now has rows of data for any new IDs only.

[Kushal_Chawda]

Yes, you can use the variable in SQL statement. Can you please elaborate further? You can do something like below

SQL

Select  ID

FROM schema.tablename

where ID not in '$(variable)'

[AyCe1082]

Hi, I was wondering more about creating the loop itself (and having it compatible with the query). The loop would go through a list of numeric IDs one at a time while the SQL load is happening or if it is possible to have the entire list.

The list could be something like

2213563

2213423

2231343

2245234

2235446

3345336

etc. and during the SQL load the variable would check all the IDs and only load in rows that don't match them. The list could be very large, possibly in the thousands so I am not sure if I should be using a loop or if the entire list can be used.

[Kushal_Chawda]

From where your list will come?

[AyCe1082]

The list comes from a cmd execution that writes a list of PDF filenames from folders on the server (which contain the IDs in the name) to a text file and then breaks up the filenames to get the ID as its own column.

Then each SQL load will check the IDs from that list and only pull rows with new IDs from the DB, so the size of each query is kept to a minimum and NPrinting isn't generating the same PDFs on every reload.

[Kushal_Chawda]

one option will be read the text file and store the values as concatenated list in variable and then use that variable in SQL where clause

List:

load concat(chr(39)&ID&chr(39),',') as List

FROM textfile;

let vList = peek('List',0,'List');

SQL

Select  ID

FROM schema.tablename

where ID not in '$(vList)';

[AyCe1082]

I got a "string literal too long" error unfortunately.

[Kushal_Chawda]

may be typo.

List:

load concat(chr(39)&ID&chr(39),',') as List

FROM textfile;

let vList = peek('List',0,'List');

SQL

Select  ID

FROM schema.tablename

where ID not in ($(vList));

[AyCe1082]

I use the following code and do get the ID numbers but the list is extremely long because there are I think a few thousand IDs.

PDF:
LOAD 
     subfield([@40:n],'_',2) as report_matchdate,
     subfield([@40:n],'_',3) as report_matchname,
     subfield([@40:n],'_',4) as report_gameId,
     subfield([@40:n],'_',5) as report_version,
     replace(subfield([@40:n],'_',6),'.pdf','') as report_language,
     if(num([@21:38])<1000000,'repeat','complete') as report_state
FROM
[...\Match_Report\filelist_ORA1.txt]
(fix, codepage is 1252, header is 9 lines) where num([@21:39])>0 and num([@21:39])<10000000 ;

PDFList:
load concat(chr(39)&report_gameId&chr(39),',') as PDFlist
resident PDF;

let vList = peek('PDFlist',0,'PDFlist');

Then in the SQL load the line used is

AND   load.GAME_CODE not in '$(vList)'

[cwolf]

Of course, SQL strings are limited in length. Oracle has a limit of 409600 for SQL strings.

Your list has a lot of duplicate values. Use:

PDFList:
load concat(distinct chr(39) & report_gameId & chr(39),',') as PDFlist
resident PDF where len(report_gameId)>0;

In the sql statement the list must be enclosed by parentheses:

where ... GAME_CODE not in ('$(vList)');