Fonction IndexCol

Fonction IndexCol

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

La syntaxe de la fonction IndexCol est la suivante :

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

Où le premier argument se résout en entier et les éléments qui composent <<expr_list>> correspondent au nombre de valeurs possibles du premier argument. L'un de ces éléments est ensuite utilisé dans l'instruction SQL selon la valeur du premier argument.

Par exemple, si l'argument <<integer_literal>> présente trois valeurs possibles, trois arguments doivent être présents 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 sur une instruction CASE référençant des variables. Vous pouvez modéliser la fonction IndexCol dans le fichier de référentiel (.rpd) ou directement dans une colonne de rapport. Vous pouvez imbriquer plusieurs fonctions IndexCol de façon à former une seule instruction.

Avantages de la fonction

Un calcul utilisant une instruction <<case when>> est transmis au code SQL physique dans son intégralité. Par comparaison, la fonction IndexCol transmet uniquement la colonne ou l'expression requise à la base de données. Cela est dû au fait que la fonction IndexCol est évaluée avant que le code SQL physique ne soit généré.

En cas de combinaison avec des invites de variable, ce qui permet d'effectuer une sélection dans une liste de valeurs, vous pouvez modifier significativement la structure de rapport sans que cela ne vous coûte au niveau des performances.

L'un des inconvénients de la fonction IndexCol est que vous ne pouvez pas l'utiliser avec l'opérateur "like" dans les calculs d'entier, bien que vous puissiez employer "like" dans la liste des expressions. Si un calcul d'entier requiert l'opérateur "like", vous devez utiliser une instruction CASE à la place.

Exemple :

Supposons qu'une variable de session nommée PREFERRED_CURRENCY définit la devise préférée d'un utilisateur, puis que selon la valeur de cette variable de session, l'élément Revenue est affiché dans la devise indiquée par l'utilisateur.

Deux calculs ont été créés pour renvoyer la devise correcte selon la valeur de la variable de session.

Le premier s'appuie sur une instruction CASE, comme dans l'exemple suivant :

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 l'exemple suivant :

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

Puisque le premier argument de la fonction IndexCol doit se résoudre en un entier, une instruction CASE est utilisée pour la résolution.

Lorsqu'une requête est exécutée avec le calcul de l'instruction CASE, l'intégralité de l'instruction est transmise à la base de données car l'instruction est évaluée au moment de l'exécution. 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 INDEXCOL

Avec l'avènement des paramètres, la fonction IndexCol peut désormais être utilisée dans les classeurs.

Dans cet exemple, la fonction IndexCol est utilisée pour modifier le niveau de finesse de la période dans une visualisation.