Bruke funksjonen IndexCol

CASE-setninger brukes ofte i Oracle Analytics når en beregning utvides basert på en variabelverdi. Når en CASE-setning refererer til en variabel, er det bedre å bruke funksjonen IndexCol i stedet for å forbedre effektiviteten i den genererte SQL-koden. Dette emnet beskriver funksjonen IndexCol og når den skal brukes.

Om funksjonen IndexCol

Du bruker funksjonen IndexCol når kolonnene eller verdiene i en beregning varierer avhengig av verdien for en økt-, register- eller presentasjonsvariabel.

Dette er syntaksen for funksjonen IndexCol::

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

Det første argumentet løses til et heltall, og elementene som utgjør <<expr_list>>, tilsvarer antallet mulige verdier for det første argumentet. Ett av disse elementene brukes deretter i SQL-setningen, basert på verdien for det første argumentet.

Hvis argumentet <<integer_literal>> for eksempel har tre mulige verdier, må det være tre argumenter i argumentet <<expr_list>> – ett for hver mulige verdi for <<integer_literal>>.

Det første argumentet er ofte basert på verdien for en øktvariabel eller en CASE-setning som refererer til variabler. Du kan modellere funksjonen IndexCol i registerfilen (RPD) eller direkte i en rapportkolonne. Du kan nøste flere IndexCol-funksjoner for å danne én setning.

Fordeler med funksjonen IndexCol

En beregning som bruker en setning av typen <<case when>>, dyttes til den fysiske SQL-koden i sin helhet. Funksjonen IndexCol dytter derimot bare den nødvendige kolonnen eller det nødvendige uttrykket ned til databasen. Dette skyldes at funksjonen IndexCol evalueres før den fysiske SQL-koden genereres.

Når den kombineres med ledetekster for variabler, som gjør det mulig å velge fra en verdiliste, kan du endre rapportstrukturen betydelig uten at det går ut over ytelsen.

En ulempe med funksjonen IndexCol er at du ikke kan bruke den med like i heltallsberegninger, selv om du kan bruke like i listen over uttrykk. Hvis en heltallsberegning krever like, må du bruke en CASE-setning i stedet.

Eksempel

Anta at det finnes en øktvariabel kalt PREFERRED_CURRENCY som angir den foretrukne valutaen for en bruker. Deretter vises inntekter i valutaen brukeren har angitt, basert på verdien av øktvariabelen.

To beregninger er opprettet for å returnere riktig valuta basert på verdien av øktvariabelen.

Den første bruker en CASE-setning, som vist her:

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

Den andre bruker funksjonen IndexCol, som vist her:

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

Ettersom det første argumentet i funksjonen IndexCol må løses til et heltall, brukes en CASE-setning for løsingen.

Ved kjøring av en spørring som bruker beregningen med CASE-setning, dyttes hele CASE-setningen ned til databasen, ettersom CASE-setningen evalueres under kjøring. I enkelte tilfeller fører dette til problemer med optimalisatoren.

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

Arbeidsbøker og IndexCol

Du kan bruke funksjonen IndexCol i arbeidsbøker.

I dette eksempelet brukes funksjonen IndexCol til å endre periodeinndelingen i en visualisering:

Opprett en parameter som skal brukes som kolonneutvelger, for å velge periodeinndelingen. I dette tilfellet er den Måned eller Kvartal.

.jpg

Opprett en egendefinert beregning for utførelse av funksjonen IndexCol. Her er beregningen følgende:

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

Beskrivelse av GUID-1EBB2C95-3D23-48C6-9754-EF968AFAFE98-default.jpg følger
.jpg

Legg til parameteren på filterlinjen i en arbeidsbok. Deretter kan brukerne endre inndelingen i en rapport ved å velge Måned eller Kvartal fra kolonneutvelgerfilteret.

.jpg