Use a List of Values to Initialize a Session Variable

You can configure a session variable's initialization block to initialize a session variable with a list of values.

To configure an initialization block to initialize a session variable with a list of values, in the initialization block configuration you must select Variable names and values in the Query Returns field.

When you select the Variable names and values field, the Query Returns field is displayed. Selecting the Cache Query result option puts the query's results in a main memory cache. The Oracle Analytics query engine uses the cached results for subsequent sessions. This can reduce session startup time. However, the cached results might not contain the most current session variable values. If every new session needs the most current set of session variables and their corresponding values, you should clear this option.

Note:

To avoid errors, be sure that your initialization block doesn't contain NULL values, and that the query's results set doesn't contain NULL values.

The information and example in this topic pertain to Logical SQL. If you're using Physical SQL to initialize a variable with a list of values, then use the VALUELISTOF function.

For example, to get the customers assigned to the user names in the variable LIST_OF_USERS, use the following SQL statement in the initialization query:

SELECT 'LIST_OF_USERS', USERID
FROM RW_SESSION_VARS
WHERE NAME='STATUS' AND VALUE='FULL-TIME'

This SQL statement populates the variable LIST_OF_USERS with a colon-separated list of the values JOHN and JANE (for example, JOHN:JANE). You can then use this variable in a filter, as shown in the following WHERE clause:

WHERE TABLE.USER_NAME = valueof(NQ_SESSION.LIST_OF_USERS)

The variable LIST_OF_USERS contains a list of one or more values. The physical IN clause replaces the logical WHERE clause as shown in the following statement:

WHERE TABLE.USER_NAME IN ('JOHN', 'JANE')

Select 'LIST_OF_CUSTOMERS', Customer_Name from RW_CUSTOMERS where RW.CUSTOMERS.USER_NAME in (VALUELISTOF(NQ_SESSION.LIST_OF_USERS))

To filter by specific values in a list, use ValueNameof. The first value is 0, not 1. For example:

Select 'LIST_OF_CUSTOMERS', Customer_Name from RW_CUSTOMERS where RW.CUSTOMERS.USER_NAME in '(ValueNameOf(0,NQ_SESSION.LIST_OF_USERS))