Use data from resident load in SQL query

[andrewmo]

Hello

Can someone direct me on how to use a resident load as part of an IN statement in an SQL where clause?

I have a primary key that is populated in a load statement that I wan to utilize in the where statement of an sql query.

Here is an example of the script:

IDSTG:

LOAD        ID 

FROM       excelsheet; //contains 500 ID values

LET vID = PEEK('ID','IDSTG');

DEMO:

LOAD          DISTINCT *;

SQL

SELECT     ID, Date, Name

FROM         Tbl1

WHERE      ID IN ('$(vID)')

I want to use vID like it is a temp table that can be referenced in the SQL script.

[its_anandrjs]

You can use where exists for this rather than IN clause.

[its_anandrjs]

This ways

IDSTG:

LOAD      

ID as IDloading

FROM     excelsheet; //contains 500 ID values

DEMO:

LOAD     DISTINCT *

WHERE Exists( IDloading,ID )

SQL

SELECT   ID, Date, Name

FROM      Tbl1;

[andrewmo]

Won't this method still pull everything from the SQL query then apply the WHERE Exists afterwards?

Tbl1 is a large table that take a lot of time to query against.

[el_aprendiz111 · Accepted solution]

Hi Andrew

1 exm:

TMP:
LOAD * Inline
[
ID,FIELD
1,A
2,C
11,V
34,N
25,U
12,P
18,O
];

;
[SUM]: LOAD Concat(CHR(39) & ID & CHR(39),',') AS ID Resident TMP;


LET v_IN_ID = trim(Peek('ID'));


DROP Table TMP, SUM;

TRACE SELECT  ID, Date, Name FROM Tbl1 WHEREID IN (  $(v_IN_ID) );

[andrewmo]

That appears to do the trick. Thanks much!

[its_anandrjs]

NO it is also a good technic to load data from selected ids where you can load the data on the SQL tables it self.

[andrewmo]

It appears to be pulling everything then applying the Where exists afterwards as I can see in the Script Execution Process the number of lines fetched when executing the SQL portion is higher than the number of IDs. Also, I do not have access to write to tables in that SQL server thus is why I needed to figure out how to apply the IDs I needed only.

[Anonymous]

So this solution only seems to work if the list of values in the IN Statement is less than 1000.  If you have more than 1000 values the sql errors out.  Is there anyway around this limit?