About
A server variable can be initialized to a list of values (vector) with the row-wise initialization option of an Init block.
As you see, the values can be cached then be careful with diagnostic.
Articles Related
How to
Populates it
SELECT
MY_VARIABLE_NAME,
MY_VALUE
FROM
MY_TABLE
This SQL statement populates the variable MY_VARIABLE_NAME with a list of value of MY_VALUE, separated by colons.
Example MY_TABLE:
MY_VARIABLE_NAME | MY_VALUE |
---|---|
GROUPTEST | ADMIN |
GROUPTEST | XMLP_ADMIN |
By clicking on the test button, you will get:
Use it in an Analytics (SQL)
In a filter, add the following:
MY_COLUMN IN VALUELISTOF(NQ_SESSION."MyRowWiserVariable")
Supported since 11g.
Use it in the repository
In a formula column
with a case when:
CASE WHEN 'ADMIN' = VALUEOF(NQ_SESSION."GROUPTEST") THEN 'Permitted' ELSE 'Not Permitted' END
will be transformed in
CASE when 'ADMIN' in ('ADMIN', 'XMLP_ADMIN') then 'Permitted' else 'Not Permitted' end
In a predicate
Once populate, you can use the variable:
- In a filter
where TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME)
- In a case when
CASE WHEN TABLE.MY_COLUMN = valueof(NQ_SESSION.MY_VARIABLE_NAME) ELSE ... END
The logical equality clause expands into a physical IN clause such as:
where TABLE.USER_NAME in ('JOHN', 'JANE').
Support
Restricted to usage with equality comparisons
When you try to use a row-wise variable in a column formula, you will throw this error:
State: HY000. Code: 2057371920.
[NQODBC] [SQL_STATE: HY000]
[nQSError: 10058] A general error has occurred.
[nQSError: 42040] The session variable, NQ_SESSION.MY_VARIABLE, is defined as Row-Wise Initialization.
It is restricted to usage with equality comparisons. (HY000)
To avoid it in 11g, you can use the function VALUELISTOF.