In Oracle Analytics, Case statements are often used when a calculation "branches" based on a variable value. When a variable is referenced in a Case statement, it's preferable to use the IndexCol function instead to improve the efficiency of the generated SQL code. This post describes the IndexCol function and when to use it.
You use the IndexCol function when the columns or values in a calculation vary depending on the value of a session, repository, or presentation variable.
The syntax of the IndexCol function is
Where the first argument resolves to an integer and the items that comprise the <<expr_list>> correspond to the number of possible values of the first argument. One of these items is then used in the SQL statement based on the value of the first argument.
For example, if the <<integer_literal>> argument has 3 possible values, then there must be 3 arguments in the <<expr_list>> argument, one for each possible value of <<integer_literal>>.
The first argument is often based on the value of a session variable or a Case statement in reference to variables. You can model the IndexCol function in the repository (.rpd) file or directly in a report column. You can nest multiple IndexCol functions to form a single statement.
A calculation using a <<case when>> statement is pushed to the physical SQL code in its entirety. By comparison, the IndexCol function pushes down only the required column or expression to the database. This is because the IndexCol function is evaluated before the physical SQL code is generated.
When combined with variable prompts, which allow selection in a list of values, you can significantly modify the report structure without any increased cost on performance.
One drawback with the IndexCol function is that you can't use it with "like" in integer calculations, although you can use "like" in the list of expressions. If an integer calculation requires a "like," you must use a Case statement instead.
Assume there's a session variable called PREFERRED_CURRENCY that sets the preferred currency for a user, then based upon the value of the session variable, Revenue is displayed in the currency specified by the user.
Two calculations have been created to return the correct currency based upon the value of the session variable.
The first uses a Case statement, as in this example:
WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'USD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd"
WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'EUR' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur"
WHEN VALUEOF(NQ_SESSION."PREFERRED_CURRENCY") = 'AUD' THEN "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud"
The second uses the IndexCol function as in this example.
WHEN 'USD' THEN 0
WHEN 'EUR' THEN 1
WHEN 'AUD' THEN 2
"01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Usd", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Eur", "01 - Sample App Data (ORCL)".""."BISAMPLE"."F19 Rev. (Converted)"."Revenue_Aud")
Because the first argument of the IndexCol function must resolve to an integer, a Case statement is used for the resolution.
When a query is run using the Case statement calculation, the entirety of the Case statement is pushed down to the database, because the Case statement is evaluated at runtime. In some cases, this causes issues with the optimizer.
SAWITH0 AS (select sum(case when 'USD' = 'USD' then T42437.Revenue_Usd when 'EUR' = 'USD' then T42437.Revenue_Eur when 'AUD' = 'USD' then T42437.Revenue_Aud else NULL end ) as c1,
T42412.Office_Dsc as c2,
T42412.Office_Key as c3
BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ ,
BISAMPLE.SAMP_REVENUE_CURR_F T42437 /* F19 Rev. (Converted) */
where ( T42412.Office_Key = T42437.Office_Key )
group by T42412.Office_Dsc, T42412.Office_Key),
SAWITH1 AS (select 0 as c1,
D1.c2 as c2,
D1.c1 as c3,
D1.c3 as c4
select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3 from ( select D1.c1 as c1,
D1.c2 as c2,
D1.c3 as c3
order by c2 ) D1
The same query run using the IndexCol function pushes down only the expression needed to satisfy the query, because the IndexCol function is resolved prior to SQL generation. This helps avoid issues with the Optimizer.
With the advent of parameters, the IndexCol function can now be used in DV.
In this example, the IndexCol function is used to change the period granularity in a visualization.
Create a parameter to be used as the column selector to select the period grain, in this case, either 'Month' or 'Quarter'.
Next, create a custom calculation to perform the IndexCol function. Here the calculation is
indexcol(case when @parameter("Time Selector Value")('Month')='Month' then 0 else 1 end, "HCM - Workforce Core"."Time"."Month Name", "HCM - Workforce Core"."Time"."Quarter")
Put the parameter in the filter bar of a workbook. Users can change the granularity of a report by selecting either 'Month' or 'Quarter' from the column selector filter.