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. Good news!! 11g seems to have a fix for this.
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.
