Utilizarea funcţiei IndexCol
În Oracle Analytics, instrucţiunile CASE sunt utilizate adesea atunci când un calcul se ramifică pe baza valorii unei variabile. Atunci când se face referire la o variabilă în instrucţiunea CASE, se preferă utilizarea funcţiei IndexCol în schimb, pentru a îmbunătăţi eficienţa codului SQL generat. Acest subiect descrie funcţia IndexCol şi când se utilizează.
Despre funcţia IndexCol
Utilizaţi funcţia IndexCol atunci când coloanele sau valorile dintr-un calcul variază, în funcţie de valoarea variabilei dintr-o sesiune, un repository sau o prezentare.
Sintaxa funcţiei IndexCol este:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Primul argument rezolvă un număr întreg, iar elementele care compun <<expr_list>> corespund numărului de valori posibile pentru primul argument. Unul dintre aceste elemente este utilizat apoi în instrucţiunea SQL, în funcţie de valoarea primului argument.
De exemplu, dacă argumentul <<integer_literal>> are trei valori posibile, trebuie să existe trei argumente în argumentul <<expr_list>>, câte unul pentru fiecare valoare posibilă pentru <<integer_literal>>.
Primul argument se bazează, de obicei, pe valoarea unei variabile de sesiune sau pe o instrucţiune CASE ca referinţă la variabile. Puteţi modela funcţia IndexCol din fişierul de repository (RPD) sau direct într-o coloană a raportului. Puteţi imbrica mai multe funcţii IndexCol pentru a forma o singură instrucţiune.
Beneficiile funcţiei IndexCol
Un calcul care utilizează o instrucţiune <<case when>> este trimis integral către codul SQL fizic. Prin comparaţie, funcţia IndexCol transmite doar coloanele sau expresiile necesare către baza de date. Aceasta deoarece funcţia IndexCol este evaluată înainte de generarea codului SQL fizic.
Prin combinaţie cu prompturile variabile, care permit selecţia dintr-o listă de valori, puteţi modifica semnificativ structura raportului, fără a creşte costul performanţei.
Un dezavantaj al funcţiei IndexCol este că nu o puteţi utiliza cu like în calculele numerelor întregi, deşi puteţi utiliza like în lista de expresii. În cazul în care calculul unui număr întreg necesită un like, puteţi utiliza în schimb instrucţiunea CASE.
Exemplu
Să presupunem că există o variabilă de sesiune denumită PREFERRED_CURRENCY care setează moneda preferată pentru un utilizator. În acest caz, pe baza valorii variabilei de sesiune, Venitul este afişat în moneda specificată de utilizator.
Au fost create două calcule pentru a returna moneda corectă, pe baza valorii variabilei de sesiune.
Primul utilizează o instrucţiune CASE, astfel:
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
ENDAl doilea utilizează funcţia IndexCol, astfel:
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")Deoarece primul argument al funcţiei IndexCol trebuie rezolvat sub forma unui număr întreg, se utilizează o instrucţiune CASE pentru rezolvare.
Dacă o interogare este rulată utilizând calculul instrucţiunii CASE, întreaga instrucţiune CASE este trimisă către baza de date, deoarece instrucţiunea CASE este evaluată la runtime. În unele cazuri, aceasta cauzează probleme optimizorului.
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 ) D1Registre de lucru şi IndexCol
Puteţi utiliza funcţia IndexCol în registrele de lucru.
În acest exemplu, funcţia IndexCol este utilizată pentru a modifica granularitatea unei perioade într-o vizualizare:
- Creaţi un parametru care să fie utilizat ca selector pentru coloană, pentru a selecta granularitatea pentru perioadă, în acest caz, Lună sau Trimestru.
- Creaţi un calcul personalizat pentru a efectua funcţia
IndexCol. Aici, calculul este: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") - Adăugaţi parametrul în bara de filtrare a unui registru de lucru. Utilizatorii pot modifica granularitatea unui raport selectând Lună sau Trimestru din filtrul selectorului de coloane.
