Használja az IndexCol függvényt
Az Oracle Analytics rendszerben gyakran használják a CASE utasításokat, amikor egy számítás egy változó értéke alapján elágazik. Ha egy változóra hivatkoznak egy CASE utasításban, a generált SQL-kód hatékonyságának javítása érdekében célszerű inkább az IndexCol függvényt használni. Ez a témakör az IndexCol függvényt írja le, és azt, hogy mikor érdemes használni.
Tudnivalók az IndexCol függvényről
Az IndexCol függvényt akkor használja, ha a számítás oszlopai vagy értékei a munkamenet, az adattár vagy a megjelenítési változó értékétől függően változnak.
Az IndexCol függvény szintaxisa a következő:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Ahol az első argumentumnak egy egész számnak kell lennie, és az <<expr_list>> kódot tartalmazó elemek megfelelnek az első argumentum lehetséges értékeinek. Ezen elemek egyike az első argumentum értéke alapján kerül felhasználásra az SQL-utasításban.
Például, ha az <<integer_literal>> argumentumnak három lehetséges értéke van, akkor három argumentumnak kell lennie az <<expr_list>> argumentumban, azaz egy az <<integer_literal>> minden lehetséges értékéhez.
Az első argumentum gyakran egy munkamenet-változó értékén vagy egy változókra hivatkozó CASE utasításon alapul. Az IndexCol függvényt az adattárban (RPD) vagy közvetlenül egy kimutatásoszlopban modellezheti. Több IndexCol függvényt is egymásba ágyazhat egyetlen utasítás létrehozásához.
Az IndexCol függvény előnyei
A <<case when>> utasítást használó számítások teljesen a fizikai SQL-kódba kerülnek. Összehasonlításképpen, az IndexCol függvény csak a szükséges oszlopot vagy kifejezést továbbítja az adatbázisba. Ennek az az oka, hogy az IndexCol függvény a fizikai SQL-kód előállítása előtt kiértékelésre kerül.
Változó promptokkal kombinálva, amelyek lehetővé teszik az értéklistában történő kiválasztást, jelentősen módosíthatja a kimutatás struktúráját anélkül, hogy a teljesítményköltség megnőne.
Az IndexCol függvény egyik hátránya, hogy nem használható a like értékkel az egész számok számításánál, bár a like értéket használhatja a kifejezések listájában. Ha egy egész szám számítása like értéket igényel, akkor helyette CASE utasítást kell használnia.
Példa
Tegyük fel, hogy van egy PREFERRED_CURRENCY nevű munkameneti változó, amely beállítja egy felhasználó előnyben részesített pénznemét. Ezután a munkameneti változó értéke alapján a Bevétel a felhasználó által megadott pénznemben jelenik meg.
Két számítás jött létre a megfelelő pénznem visszaadásához a munkamenet-változó értéke alapján.
Az első a CASE utasítást használja így:
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
ENDA második az IndexCol függvényt használja így:
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")Mivel az IndexCol függvény első argumentumának egész számnak kell lennie, a megoldás CASE utasítást használ.
Ha egy lekérdezést a CASE utasítás szerinti számítással futtat, a CASE utasítás teljes egésze lekerül az adatbázisba, mivel a CASE utasítás kiértékelése futás közben történik. Bizonyos esetekben ez problémákat okoz az optimalizálóval kapcsolatban.
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 ) D1Munkafüzetek és az IndexCol
Az IndexCol függvény munkafüzetekben is használható.
Ebben a példában az IndexCol függvényt használjuk a megjelenítés időszaki részletességének módosítására:
- Hozzon létre egy paramétert, amelyet oszlopválasztóként fog használni az időszaki részletesség kiválasztásához – ebben az esetben a Hónap vagy a Negyedév.
- Hozzon létre egy egyéni számítást az
IndexColfüggvény végrehajtásához. Íme a számítás: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") - Adja hozzá a paramétert egy munkafüzet szűrősávjához. A felhasználók módosíthatják a kimutatások részletességét, ha az oszlopválasztó szűrőben a Hónap vagy a Negyedév értéket választja ki.
