De functie IndexCol gebruiken

In Oracle Analytics worden vaak CASE-statements gebruikt als een berekening opsplitst op basis van een variabele waarde. Wanneer er in een CASE-statement naar een variabele wordt verwezen, heeft het de voorkeur om de functie IndexCol te gebruiken om de efficiëntie van de gegenereerde SQL-code te verbeteren. In dit onderwerp wordt de functie IndexCol beschreven en wordt uitgelegd wanneer u deze moet gebruiken.

Over de IndexCol-functie

U gebruikt de functie IndexCol wanneer de kolommen of waarden in een berekening variëren op basis van de waarde van een sessie-, repository- of presentatievariabele.

De syntaxis van de functie IndexCol is:

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

Waarbij het eerste argument naar een geheel getal wordt herleid en de items waaruit de <<expr_list>> bestaat verwijzen naar het aantal mogelijke waarden van het eerste argument. Eén van deze items wordt vervolgens gebruikt in het SQL-statement op basis van de waarde van het eerste argument.

Als het argument <<integer_literal>> bijvoorbeeld drie mogelijke waarden heeft, moeten er drie argumenten zijn in het argument <<expr_list>> ; één voor elke mogelijke waarde van <<integer_literal>>.

Het eerste argument is vaak gebaseerd op de waarde van een sessievariabele of een CASE-statement met betrekking tot variabelen. U kunt de functie IndexCol vormgeven in het repository-bestand (.rpd) of rechtstreeks in een rapportkolom. U kunt meerdere IndexCol-functies nesten om één statement te vormen.

Voordelen van de IndexCol-functie

Een berekening die een statement <<case when>>gebruikt, wordt volledig naar de fysieke SQL-code doorgevoerd. Ter vergelijking: de functie IndexCol voert alleen de vereiste kolom of uitdrukking door naar de database. Dit komt omdat de functie IndexCol wordt geëvalueerd, voordat de fysieke SQL-code wordt gegenereerd.

Bij een combinatie met variabeleprompts die een selectie in een lijstweergave toestaan, kunt u de rapportstructuur aanzienlijk bewerken zonder in te boeten op de prestaties.

Een nadeel van de functie IndexCol is dat u deze niet kunt gebruiken met vergelijkbaar in berekeningen met gehele getallen, hoewel u wel vergelijkbaar kunt gebruiken in de lijst uitdrukkingen. Als een berekening met gehele getallen een vergelijkbaar nodig heeft, moet u in plaats daarvan een CASE-statement gebruiken.

Voorbeeld

Neem aan dat er een sessievariabele is genaamd PREFERRED_CURRENCY waarmee de gewenste valuta wordt ingesteld voor een gebruiker. Vervolgens wordt op basis van de waarde van de sessievariabele 'Opbrengst' weergegeven in de valuta die is opgegeven door de gebruiker.

Er zijn twee berekeningen gemaakt om de juiste valuta weer te geven op basis van de valuta van de sessievariabele.

De eerste gebruikt een CASE-statement, zoals dit:

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

De tweede gebruikt de functie IndexCol zoals dit:

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

Omdat het eerste argument van de functie IndexCol naar een geheel getal herleid moet worden, wordt er een CASE-statement voor de oplossing gebruikt.

Wanneer een query met de berekening van het CASE-statement wordt uitgevoerd, wordt het volledige CASE-statement doorgevoerd naar de database, omdat het CASE-statement tijdens de runtime wordt geëvalueerd. In sommige gevallen veroorzaakt dit problemen met de optimizer.

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

Werkmappen en IndexCol

U kunt de functie IndexCol gebruiken in werkmappen.

In dit voorbeeld wordt de functie IndexCol gebruikt om de granulatie van de periode in een visualisatie te wijzigen:

Maak een parameter die wordt gebruikt als de kolomselectie voor het selecteren van de granulatie van de periode; in dit geval 'maand' of 'kwartaal'.

.jpg

Maak een aangepaste berekening om de functie IndexCol uit te voeren. De berekening staat hier:

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

Beschrijving van GUID-1EBB2C95-3D23-48C6-9754-EF968AFAFE98-default.jpg volgt hierna
.jpg

Voeg de parameter toe aan de filterbalk van een werkmap. Gebruikers kunnen dan de granulatie van een rapport wijzigen door maand of kwartaal in het selectiefilter van de kolom te selecteren.

.jpg