Store sql query in variable

[qlikuser22]

Hi 

I have a select statement written in SQL. 

I want help to store the query result in a variable and use the variable in another SQL query. Please help me if there is any solution. 

Example:

Let abc = select * from table;

SQL select * from table where a =  (§abc); 

[mfchmielowski]

Hi.

SQL query results are tables. You cannot store them as variable, but you can use them.

IT depends on the result the query gives. If you have the query that returns one row you can use peek() function. If there are many rows returned use for each loop.

IF the sql query contains personId column you can write:

Load *;sql

Select personId From persons;

For each vPersonId in FieldValueList('personId')

Load *; sql select personEmail From persons WHERE personId = $(vPersonId) ;

Next vPersonId;

[qlikuser22]

Thanks a lot. I have year month date column, I want to concatenate these and find maximum (latest partition) using SQL Hive query in qliksense load. 

Any help for this. 

[phapalesaurabh]

Hello, 

 you can create a SET variable to load the SQL Text in a variable and use it

You can use below code snippet.

Example:

SET abc = 'select * from table';

SQL select * from table where a =  (§abc); 

[qlikuser22]

should we use single quotes to load the select statement. like, SET abc = ' SQL Select * from table a'; else SET abc = sql select * from abc; 

[phapalesaurabh]

Without quote will also work. 

[qlikuser22]

No it's not fetching any data. I got Lines fetched: 0 Any help. 

[phapalesaurabh]

Is it possible to share your script log  ?

 Query is getting executed from Qlik Side but I think, there some issue with the sql which you are passing.

[qlikuser22]

Sure. 

Let abc = 'SQL select max(concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0)))
from table';

SQL select * from table where concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0))) =  §(abc); 

Not able to get any output. 

[phapalesaurabh]

Can you try again using below statement ?

SET abc = 'Select max(concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0)))
from table';

SQL select * from table where concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0))) = (§(abc));