פונקציית IndexCol

ב-Oracle Analytics, לעתים קרובות נעשה שימוש במשפטיCase כאשר חישוב "מסתעף" במבוסס על ערך משתנה. כאשר במשפט Case יש הפניה למשתנה, עדיף להשתמש בפונקציה IndexCol מקום לשפר את היעילות של קוד SQL שהופק. נושא זה מתאר את פונקציית IndexCol ומתי משתמשים בה.

פונקציית IndexCol

משתמשים בפונקציית IndexCol כאשר העמודות או הערכים בחישוב משתנים על פי ערך המשתנה של מושב עבודה, מאגר נתונים או מצגת.

התחביר של פונקציית IndexCol הוא

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

כאשר הארגומנט הראשון נפתר למספר חיובי שלם והפריטים שמרכיבים את <<expr_list>> מקבילים למספר הערכים האפשריים של הארגומנט הראשון. לאחר מכן נעשה שימוש באחד מן הערכים הללו במשפט ה-SQL במבוסס על ערך הארגומנט הראשון.

לדוגמה, אם לארגומנט <<integer_literal>> יש שלושה ערכים אפשריים, אז חייבים להיות שלושה ארגומנטים בארגומנט <<expr_list>> , אחד לכל ערך אפשרי של <<integer_literal>>.

הארגומנט הראשון, לעתים קרובות מבוסס על הערך של משתנה מושב עבודה או משפט Case ביחס למשתנים. תוכל ליצור מודל לפונקציית IndexCol בקובץ מאגר הנתונים (.rpd) או ישירות בעמודת דוח. תוכל לקנן מספר פונקציות IndexCol כדי ליצר משפט יחיד.

יתרונות הפונקציה

חישוב שעושה שימוש במשפט <<case when>> נדחף אל קוד ה-SQL הפיזי במלואו. לשם השוואה, פונקציית IndexCol דוחפת למטה אל מסד הנתונים רק את העמודה או הביטוי הדרושים. זאת, כיוון שהערכת פונקציית IndexCol מתבצעת לפני הפקת קוד ה-SQL.

כאשר מבנה הדוח משולב עם מנחי משתנים שמאפשרים בחירה מתוך רשימת ערכים, תוכל לשנות אותו באופן משמעותי ללא כל עלות מוגדלת על ביצועים.

חיסרון אחד של פונקציית IndexCol הוא שלא תוכל להשתמש בה עם "דומה" בחישובי המספר השלם, למרות שתוכל להשתמש ב"דומה" ברשימת הביטויים. אם חישוב מספר חיובי שלם דורש "דומה", עליך להשתמש במשפט Case במקום זאת.

דוגמה:

נניח שיש משתנה מושב עבודה שנקרא PREFERRED_CURRENCY שמגדיר את המטבע המועדף בשביל משתמש, ואז במבוסס על הערך של משתנה מושב העבודה, מוצגת הכנסה במטבע שצוין על-ידי המשתמש.

נוצרו שני חישובים כדי להחזיר את המטבע הנכון המבוסס על הערך של משתנה מושב העבודה.

הראשון עושה שימוש במשפט Case, כמו בדוגמה הבאה:

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

השני עושה שימוש בפונקציית IndexCol כמו בדוגמה הזאת

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

כיוון שהארגומנט הראשון של פונקציית IndexCol חייב להיפתר למספר חיובי שלם, נעשה שימוש במשפט Case בשביל הפתרון.

כשמריצים שאילתא באמצעות משפט ה-Case, המשפט כולו נדחף למטה למסד הנתונים, כיוון שהערכת משפט ה-Case מתבצעת בזמן ריצה. במקרים מסוימים, דבר זה גורם לבעיות ב-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

חוברות עבודה ו-INDEXCOL

עם הופעת הפרמטרים, ניתן עכשיו להשתמש בפונקציית IndexCol בחוברות עבודה.

בדוגמה זו, נעשה שימוש בפונקציית IndexCol כדי לשנות את רמת פירוט התקופה בהמחשה גרפית.

צור פרמטר אשר ישמש כבורר עמודות על מנת לבחור את רמת פירוט התקופה, במקרה זה, 'חודש' או 'רבעון'.

.jpg

לאחר מכן, צור חישוב מותאם אישית כדי לבצע את פונקציית IndexCol. כאן, החישוב הוא:

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

להלן תיאור של GUID-1EBB2C95-3D23-48C6-9754-EF968AFAFE98-default.jpg
.jpg

שים את הפרמטר בסרגל הסינון של חוברת עבודה. משתמשים יכולים לשנות את רמת הפירוט של דוח על-ידי בחירה ב'חודש' או 'רבעון' ממסנן בורר העמודות.

.jpg