topic Re: how to save sql select values in a variable and using that variable within a seperate sql select in QlikView

how to save sql select values in a variable and using that variable within a seperate sql select

Anonymous — Mon, 17 Aug 2015 07:51:00 GMT

Hi all,

Lets say for example I have a sql select statement,
like sql select 1,2,3 from dual.

What it returns is then the values 1,2, and 3.

Is it possible to then save these values as a variable (v_number) ) and use that variable within a seperate sql select statement?

like sql select 1,2,3 from dual where number = (select number from v_number);

is this possible?

Or do I need to use the load statement after each sql select and then do the calculations there?

thanks.

Re: how to save sql select values in a variable and using that variable within a seperate sql select

pokassov — Mon, 17 Aug 2015 07:53:43 GMT

Hi!

Somthing like this?

t1:

load

concat (val,',') as val;

sql select 1,2,3 as val from dual;

let vVal=peek('val',0,'t1');

drop table t1;

t2:

sql select * from table1

where id in ($(vVal));

Re: how to save sql select values in a variable and using that variable within a seperate sql select

Anonymous — Mon, 17 Aug 2015 08:02:16 GMT

something like:

"C_MOBILE_ACTIVE":

sql select sk_account_tran_type_no as sk_account_tran_type_no_2

from dim_account_tran_type dat

where (dat.account_tran_type_no = 2 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 3 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 14 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 15 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 16 and dat.source_ind = 'M');

and then using sk_account_tran_type_no_2 in another sql select:

sql select max(fa.sk_trn_date_no)

from fct_account_cust_trn fa

where da.sk_account_no = fa.sk_account_no

and fa.sk_account_tran_type_no = sk_account_tran_type_no_2

is this possible?

Re: how to save sql select values in a variable and using that variable within a seperate sql select

pokassov — Mon, 17 Aug 2015 08:05:02 GMT

Yes but use my above approach.

1. select data to table

2. set variable using table data

3. fetch new data from another sql.

C_MOBILE_ACTIVE:

load

concat(sk_account_tran_type_no_2,',') As sk_account_tran_type_no_2;

sql select sk_account_tran_type_no as sk_account_tran_type_no_2

from dim_account_tran_type dat

where (dat.account_tran_type_no = 2 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 3 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 14 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 15 and dat.source_ind = 'M')

or (dat.account_tran_type_no = 16 and dat.source_ind = 'M');

let vVal=peek('sk_account_tran_type_no_2',0,'C_MOBILE_ACTIVE');

and then using sk_account_tran_type_no_2 in another sql select:

sql select max(fa.sk_trn_date_no)

from fct_account_cust_trn fa

where da.sk_account_no = fa.sk_account_no

and fa.sk_account_tran_type_no in ($(vVal));