[OBIEE 11g] Row Wise Initialized Variable In OBIEE 11g And VALUE LIST OF Function

Row wise initialized variable in OBIEE 11g and VALUELISTOF Function

OBIEE up to 10g had a limitation around session variables which are row wise initialized ,in that you cannot use that variable to initialize another subsequent session variable,In Obiee11g we can do it.

For e.g. lets say you have a session (row wise initialized)  variable called “REGION” initialized as follows

select ‘REGION_LIST’ ,region_id from region_table.

Now lets say you want to initialize another row wise initialized variable based on the ‘REGION_LIST’ variable you had initialized earlier. For e.g. all the accounts belonging to the ‘REGION_LIST’ variable.

Here is the new way of doing it in  11g

 

select ‘ACCOUNT_LIST’ ,account_id from accounts where

region_id in (VALUELISTOF(NQ_SESSION.REGION_LIST)).

 

Keep in mind this will go back to the database as an in list, coma separated and the values would be in single quotes.