Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

andrewmo
Contributor

Use data from resident load in SQL query

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.

1 Solution

Accepted Solutions
el_aprendiz111
Valued Contributor

Re: Use data from resident load in SQL query

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) );


IN.png

8 Replies

Re: Use data from resident load in SQL query

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

Re: Use data from resident load in SQL query

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
Contributor

Re: Use data from resident load in SQL query

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
Valued Contributor

Re: Use data from resident load in SQL query

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) );


IN.png

andrewmo
Contributor

Re: Use data from resident load in SQL query

That appears to do the trick. Thanks much!

Re: Use data from resident load in SQL query

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
Contributor

Re: Use data from resident load in SQL query

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.

Peppermint2000
New Contributor

Re: Use data from resident load in SQL query

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?  

Community Browser