Utiliser la fonction IndexCol

À propos de la fonction IndexCol

Vous utilisez la fonction IndexCol lorsque les colonnes ou les valeurs d'un calcul varient en fonction de la valeur d'une variable de session, de référentiel ou de présentation.

La syntaxe de la fonction IndexCol est :

INDEXCOL(<<integer_literal>>, <<expr_list>>)

Où le premier argument est un entier et les éléments qui composent l'énoncé <<expr_list>> correspondent au nombre de valeurs possibles du premier argument. L'un de ces éléments est alors utilisé dans l'énoncé SQL en fonction de la valeur du premier argument.

Par exemple, si l'argument <<integer_literal>> a trois valeurs possibles, il doit y avoir trois arguments dans l'argument <<expr_list>>, un pour chaque valeur possible de <<integer_literal>>.

Le premier argument est souvent basé sur la valeur d'une variable de session ou d'un énoncé CASE en référence à des variables. Vous pouvez modéliser la fonction IndexCol dans le fichier du référentiel (RPD) ou directement dans une colonne du rapport. Vous pouvez imbriquer plusieurs fonctions IndexCol pour former un seul énoncé.

Avantages de la fonction IndexCol

Un calcul utilisant un énoncé <<case when>> est poussé vers le code SQL physique dans son intégralité. En comparaison, la fonction IndexCol ne transmet à la base de données que la colonne ou l'expression requise. En effet, la fonction IndexCol est évaluée avant que le code SQL physique ne soit généré.

En combinaison avec des invites de variable, qui permettent une sélection dans une liste de valeurs, vous pouvez modifier de manière significative la structure du rapport sans que cela n'ait d'incidence sur la performance.

Un inconvénient de la fonction IndexCol est que vous ne pouvez pas l'utiliser avec like dans les calculs sur les nombres entiers, bien que vous puissiez utiliser like dans la liste des expressions. Si un calcul sur des nombres entiers nécessite un énoncé like, vous devez utiliser un énoncé CASE à la place.

Exemple

Considérons par exemple une variable de session nommée PREFERRED_CURRENCY qui définit la devise privilégiée d'un utilisateur. Ensuite, en fonction de la valeur de la variable de session, les revenus sont affichés dans la devise spécifiée par l'utilisateur.

Deux calculs ont été créés pour retourner la devise correcte en fonction de la valeur de la variable de session.

Le premier utilise un énoncé CASE, comme dans cet exemple :

CASE

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" 

ELSE NULL

END

Le second utilise la fonction IndexCol, comme dans cet exemple :

INDEXCOL(

CASE VALUEOF("NQ_SESSION"."PREFERRED_CURRENCY")

WHEN 'USD' THEN 0

WHEN 'EUR' THEN 1

WHEN 'AUD' THEN 2

END ,

"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")

Comme le premier argument de la fonction IndexCol doit être un entier, un énoncé CASE est utilisé pour la résolution.

Lorsqu'une interrogation est exécutée à l'aide du calcul de l'énoncé CASE, l'énoncé CASE est transmis en totalité à la base de données, car l'énoncé CASE est évalué lors de l'exécution de l'interrogation. Dans certains cas, cela entraîne des problèmes avec l'optimiseur.

WITH

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

from

     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

from

     SAWITH0 D1)

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

from

     SAWITH1 D1

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

SAWITH0 AS (select sum(T42437.Revenue_Usd) as c1,

     T42412.Office_Dsc as c2,

     T42412.Office_Key as c3

from

     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

from

     SAWITH0 D1)

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

from

     SAWITH1 D1

order by c2 ) D1

Classeurs et fonction IndexCol

Vous pouvez utiliser la fonction IndexCol dans les classeurs.

Dans cet exemple, la fonction IndexCol est utilisée pour modifier la granularité de la période dans une visualisation :