Utilizar a Função IndexCol
No Oracle Analytics, as instruções CASE são frequentemente utilizadas quando um cálculo é ramificado com base num valor de variável. Quando uma variável é referenciada numa instrução CASE, é preferível utilizar a função IndexCol para melhorar a eficiência do código de SQL gerado. Este tópico descreve a função IndexCol e quando utilizá-la.
Acerca da Função IndexCol
Utilize a função IndexCol quando as colunas ou os valores num cálculo variam consoante o valor de uma variável de sessão, de repositório ou de apresentação.
A sintaxe da função IndexCol é:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Em que o primeiro argumento é decifrado para um número inteiro e os itens que compõem <<expr_list>> correspondem ao número de valores possíveis do primeiro argumento. Um destes itens é depois utilizado na instrução de SQL com base no valor do primeiro argumento.
Por exemplo, se o argumento <<integer_literal>> tiver três valores possíveis, devem existir três argumentos no argumento <<expr_list>>, um para cada valor possível de <<integer_literal>>.
O primeiro argumento é frequentemente baseado no valor de uma variável de sessão ou numa instrução CASE em referência a variáveis. Pode modelar a função IndexCol no ficheiro de repositório (RPD) ou diretamente numa coluna de relatório. Pode encadear várias funções IndexCol para formar uma única instrução.
Vantagens da Função IndexCol
Um cálculo que utiliza uma instrução <<case when>> é enviado para o código de SQL físico na sua totalidade. Em comparação, a função IndexCol envia apenas a coluna ou expressão obrigatória para a base de dados. Isto ocorre porque a função IndexCol é avaliada antes de o código de SQL físico ser gerado.
Quando combinada com prompts de variáveis, que permitem a seleção numa lista de valores, pode modificar significativamente a estrutura do relatório sem qualquer custo acrescido para o desempenho.
Uma desvantagem da função IndexCol é que não a pode utilizar com like em cálculos de números inteiros, embora possa utilizar like na lista de expressões. Se um cálculo de número inteiro requerer um like, deve utilizar uma instrução CASE.
Exemplo
Suponha que existe uma variável de sessão chamada PREFERRED_CURRENCY que define a moeda preferida de um utilizador. Em seguida, com base no valor da variável de sessão, as receitas são apresentadas na moeda especificada pelo utilizador.
Foram criados dois cálculos para devolver a moeda correta com base no valor da variável de sessão.
O primeiro utiliza uma instrução CASE, como a seguinte:
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
ENDO segundo utiliza a função IndexCol, como a seguinte:
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")Como o primeiro argumento da função IndexCol deve ser decifrado para um número inteiro, é utilizada uma instrução CASE para a decifração.
Quando uma consulta é executada utilizando o cálculo da instrução CASE, toda a instrução CASE é enviada para a base de dados, porque a instrução CASE é avaliada em runtime. Em alguns casos, isto causa problemas com o otimizador.
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 ) D1Livros e IndexCol
Pode utilizar a função IndexCol nos livros.
Neste exemplo, a função IndexCol é utilizada para alterar a granularidade do período numa visualização:
- Crie um parâmetro a utilizar como seletor de colunas para selecionar a granularidade do período, neste caso, Month ou Quarter.
- Crie um cálculo customizado para executar a função
IndexCol. O cálculo é: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") - Acrescente o parâmetro à barra de filtros de um livro. Depois, os utilizadores podem alterar a granularidade de um relatório selecionando Month ou Quarter a partir do filtro do seletor de colunas.
