Utilizzare la funzione IndexCol

Informazioni sulla funzione IndexCol

È possibile utilizzare la funzione IndexCol quando le colonne o i valori in un calcolo variano in base al valore di una variabile di sessione, repository o presentazione.

La sintassi della funzione IndexCol è la seguente:

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

Dove il primo argomento restituisce un numero intero e gli elementi che costituiscono <<expr_list>> corrispondono al numero di valori possibili del primo argomento. Uno di questi elementi viene quindi utilizzato nell'istruzione SQL basata sul valore del primo argomento.

Ad esempio, se l'argomento <<integer_literal>> ha tre valori possibili, l'argomento <<expr_list>> deve includere tre argomenti, uno per ciascun valore possibile di <<integer_literal>>.

Spesso il primo argomento si basa sul valore di una variabile di sessione o di un'istruzione CASE in riferimento alle variabili. È possibile modellare la funzione IndexCol nel file del repository (RPD) o direttamente in una colonna di report. È possibile nidificare più funzioni IndexCol per formare un'unica istruzione.

Vantaggi della funzione IndexCol

Nel codice SQL fisico viene eseguito il push di un intero calcolo che utilizza un'istruzione <<case when>>. Per la funzione IndexCol, invece, viene eseguito il push solo della colonna o dell'espressione richiesta nel database. Ciò avviene perché la funzione IndexCol viene valutata prima che il codice SQL fisico venga generato.

Se viene utilizzata in combinazione con i prompt variabile, che consentono la selezione di un valore in un elenco, è possibile modificare notevolmente la struttura del report senza compromettere ulteriormente le prestazioni.

Uno svantaggio della funzione IndexCol consiste nell'impossibilità di utilizzarla con like nei calcoli con numeri interi, sebbene sia possibile utilizzare like nella lista di espressioni. Se un calcolo con numeri interi richiede un'istruzione like, è necessario utilizzare un'istruzione CASE.

Esempio

Si supponga che esista una variabile di sessione denominata PREFERRED_CURRENCY che imposta la valuta preferita di un utente. In base al valore di questa variabile di sessione, vengono quindi visualizzati i ricavi nella valuta specificata dall'utente.

Sono stati creati due calcoli per restituire la valuta corretta in base al valore della variabile di sessione.

Il primo utilizza un'istruzione CASE simile alla seguente:

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

Il secondo utilizza la funzione IndexCol simile alla seguente:

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

Poiché il primo argomento della funzione IndexCol deve restituire un numero intero, viene utilizzata un'istruzione CASE per la risoluzione.

Quando si esegue una query utilizzando il calcolo dell'istruzione CASE, viene eseguito il push dell'intera istruzione CASE nel database, in quanto l'istruzione CASE viene valutata in fase di esecuzione. In alcuni casi questo può causare problemi con l'ottimizzatore.

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

Cartelle di lavoro e funzione IndexCol

È possibile utilizzare la funzione IndexCol nelle cartelle di lavoro.

In questo esempio la funzione IndexCol viene utilizzata per modificare la granularità del periodo in una visualizzazione: