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. 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.
No comments:
Post a Comment