Row wise initialized variable in OBIEE 11g and VALUELISTOF function

最新推荐文章于 2025-12-16 09:07:54 发布

转载最新推荐文章于 2025-12-16 09:07:54 发布 · 2.2k 阅读

文章标签：

#function #list #session #variables #database #go

BIEE11g 专栏收录该内容

54 篇文章

订阅专栏

OBIEE 11g版本中解决了早前版本关于会话变量初始化的限制，允许使用已初始化的会话变量来进一步初始化其他的会话变量。例如，在初始化了名为“REGION”的会话变量后，可以使用它来获取所有属于这些区域的账户列表。

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.