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

[Anonymous]

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.

[pokassov]

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

[Anonymous]

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?

[pokassov]

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