OAC - indexCol function in calculated column in semantic modeler

Question

Hello,

Anyone used the indexCol function in the web semantic modeler?

I tried to use it to replace a simple case in a calculated column. The calcul was valid but on deploy a get a strange error (first parameter of indexCol is not un integer)

For example to replace this case:

CASE WHEN genre = 'H' THEN 'Homme' WHEN genre = 'F' THEN 'Femme' ELSE 'NR' END

with

INDEXCOL(CASE WHEN genre = 'H' THEN 0 WHEN genre = 'F' THEN 1 ELSE 2 END , 'Homme', 'Femme', 'NR')

Thanks!

[Deleted User] · Answer

https://community.oracle.com/products/oracleanalytics/discussion/19978/oac-indexcol-function-in-calculated-column-in-semantic-modeler

Maybe the key question here is: Why are you trying to replace this? What are you trying to achieve?

INDEXCOL is a choice of solution but which issue are you trying to solve  in order to achieve which goal?

Gianni Ceresa · Answer

Going back to the linked blog, they say this:

The first argument is often based on the value of a session variable or a Case statement in reference to variables.

This means that INDEXCOL generally work with a constant number as first argument, a number that is constant for the whole execution of the query.

The error [nQSError: 23011] The first argument to IndexCol function must be a constant integer literal. (HY000) means just that: the first argument should be constant for the whole query, while your example do use a CASE WHEN with the first argument changing value (potentially) at each row of your query.

The target of the INDEXCOL function, as shown in the blog post, is to push down to the database a simpler query: if you need to pick between 2-3 different columns based on a variable or a prompt or something else that is a static criteria across the whole query, it isn't efficient to push down to the database a CASE WHEN based on a static value (the variable or the prompt value) that will then retrieve values from a single column. In that context INDEXCOL jumps in and push down to the database only the needed column, it resolve the value of the CASE WHEN before to push down a physical query to the database, and this allow the tool to optimize the query by requesting only one single column.

In your case you are trying to use INDEXCOL to generate static values, something that should be evaluated at each row of the dataset of the query. INDEXCOL not only isn't going to work (as you see it complains that the first argument should be a constant integer), but you get not a single benefit out of it, because you are still pushing down to your database a CASE WHEN on the "genre" column.

Gianni Ceresa · Answer

No, the syntax "Homme"."Femme"."NR" will definitely not work because it will try to find the presentation column NR in the presentation table Femme of the subject area Homme. And that's definitely not a thing that exist.

@User_9YNXK , in your example, what benefits do you expect from the usage of the INDEXCOL? Because you already have a CASE WHEN anyway checking the values of your column, therefore you probably get the best result by doing just a case when returning the genders "homme", "femme" and "nr" instead of a case when returning an integer that is then replaced with a literal (constant) value.