Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser22
Creator II
Creator II

Store sql query in variable

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

Labels (1)
10 Replies
mfchmielowski
Creator II
Creator II

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
Creator II
Creator II
Author

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
Partner - Contributor III
Partner - Contributor III

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
Creator II
Creator II
Author

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
Partner - Contributor III
Partner - Contributor III

Without quote will also work. 

qlikuser22
Creator II
Creator II
Author

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

phapalesaurabh
Partner - Contributor III
Partner - Contributor III

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
Creator II
Creator II
Author

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
Partner - Contributor III
Partner - Contributor III

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