Usar a Função IndexCol
No Oracle Analytics, as instruções CASE são frequentemente usadas quando um cálculo é ramificado com base em um valor de variável. Quando uma variável é referenciada em uma instrução CASE, é preferível usar a função IndexCol para melhorar a eficiência do código SQL gerado. Este tópico descreve a função IndexCol e quando usá-la.
Sobre a Função IndexCol
Você usa a função IndexCol quando as colunas ou valores em um cálculo variam, dependendo do valor de uma sessão, repositório ou variável de apresentação.
A sintaxe da função IndexCol é a seguinte::
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Em que o primeiro argumento é resolvido para um número inteiro e os itens que compreendem <<expr_list>> correspondem ao número de valores possíveis do primeiro argumento. Um desses itens é então usado na instrução SQL com base no valor do primeiro argumento.
Por exemplo, se o argumento <<integer_literal>> tiver três valores possíveis, deverá haver três argumentos no argumento <<expr_list>>, um para cada possível valor de <<integer_literal>>.
O primeiro argumento muitas vezes se baseia no valor de uma variável de sessão ou de uma instrução CASE em referência a variáveis. Você pode modelar a função IndexCol no arquivo do repositório (RPD) ou diretamente em uma coluna de relatório. Você pode aninhar várias funções IndexCol para formar uma única instrução.
Benefícios da Função IndexCol
Um cálculo usando uma instrução <<case when>> é enviado ao código SQL físico em sua totalidade. Por comparação, a função IndexCol transfere apenas a coluna ou expressão necessária para o banco de dados. Isso acontece porque a função IndexCol é avaliada antes da geração do código SQL físico.
Quando em combinação com prompts de variável, que permitem a seleção em uma lista de valores, você pode modificar significativamente a estrutura do relatório sem qualquer aumento de custo no desempenho.
Uma desvantagem da função IndexCol é que você não pode usá-la com like em cálculos com números inteiros, embora você possa usar like na lista de expressões. Se um cálculo com número inteiro exigir um like, você deverá usar uma instrução CASE.
Exemplo
Suponha que haja uma variável de sessão chamada PREFERRED_CURRENCY que define a moeda preferencial para um usuário. Então, com base no valor da variável de sessão, a Receita é exibida na moeda especificada pelo usuário.
Dois cálculos foram criados para retornar a moeda correta com base no valor da variável de sessão.
O primeiro usa uma instrução CASE, como esta:
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 usa a função IndexCol da seguinte forma:
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 resolvido para um número inteiro, uma instrução CASE é usada para a resolução.
Quando uma consulta é executada usando o cálculo da instrução CASE, a totalidade da instrução CASE é transferida para o banco de dados, porque a instrução CASE é avaliada no runtime. Em alguns casos, isso 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 ) D1Pastas de trabalho e IndexCol
Você pode usar a função IndexCol em pastas de trabalho.
Neste exemplo, a função IndexCol é usada para alterar a granularidade do período em uma visualização:
- Crie um parâmetro para usar como seletor de coluna para selecionar o detalhamento do período, neste caso, Mês ou Trimestre.
- Crie um cálculo personalizado para executar a função
IndexCol. Aqui o cálculo é o seguinte: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") - Adicione o parâmetro à barra de filtragem de uma pasta de trabalho. Os usuários podem então alterar a granularidade de um relatório selecionando Mês ou Trimestre no filtro seletor de colunas.
