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 敘述句值。您可以在儲存區域 (.rpd) 檔案中或直接在報表資料欄中建立 IndexCol 函數的模型。您可以嵌套多個 IndexCol 函數以組成單一敘述句。

此函數的優點

使用 <<case when>> 敘述句的計算式會整個被推送至實體 SQL 程式碼。相較之下，IndexCol 函數只會將必要的資料欄或表示式下推至資料庫。這是因為 IndexCol 函數會在實體 SQL 程式碼產生之前就先被評估。

與變數提示結合之後，就能從值清單中選取，如此您可大幅修改報表結構，完全不會影響效能。

IndexCol 函數的一個缺點是不能在整數計算式中與 "like" 搭配使用，但您還是可以在表示式清單中使用 "like"。整數計算式如果需要使用 "like"，您必須改用 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 敘述句下推至資料庫，因為 Case 敘述句會在執行時期進行評估。在某些情況下，這會導致最佳化處理程式發生問題。

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