Monday, January 13, 2014

Using presentation variables with in clause in direct DB request

Suppose you have defined presentation variable named CAL_QTR in prompt.

Here is how you apply filter using presentation variable using in clause
TIME.Qarter in (
(SELECT REGEXP_SUBSTR('@{CAL_QTR}', '[^,]+', 1, LEVEL) RESULT
    FROM DUAL connect by level <= length(REGEXP_REPLACE('@{CAL_QTR}', '[^,]+')) + 1 )
)