Uso de la función IndexCol
En Oracle Analytics, las sentencias CASE se suelen utilizar cuando un cálculo se ramifica según un valor de variable. Cuando se hace referencia a una variable en una sentencia CASE, es preferible utilizar la función IndexCol en lugar de mejorar la eficiencia del código SQL generado. En este tema se describe la función IndexCol y cuándo utilizarla.
Acerca de la función IndexCol
Utilice la función IndexCol cuando las columnas o los valores de un cálculo varían en función del valor de una variable de sesión, repositorio o presentación.
La sintaxis de la función IndexCol es:
INDEXCOL(<<integer_literal>>, <<expr_list>>)
Donde el primer argumento se resuelve an un entero y los elementos que componen <<expr_list>> corresponden al número de valores posibles del primer argumento. Uno de estos elementos se utiliza a continuación en la sentencia SQL según el valor del primer argumento.
Por ejemplo, si el argumento <<integer_literal>> tiene tres valores posibles, debe haber tres argumentos en el argumento <<expr_list>>, uno para cada valor posible de <<integer_literal>>.
El primer argumento suele estar basado en el valor de una variable de sesión o una sentencia CASE en referencia a variables. Puede modelar la función IndexCol en el archivo de repositorio (RPD) o directamente en una columna de informe. Puede anidar varias funciones IndexCol para formar una única sentencia.
Ventajas de la función IndexCol
Un cálculo que utilice la sentencia <<case when>> se envía al código SQL físico en su totalidad. En cambio, la función IndexCol solo envía la columna o la expresión necesaria a la base de datos. Esto es debido a que la función IndexCol se evalúa antes de que se genere el código SQL físico.
Cuando se combina con peticiones de datos de variable, que permiten la selección en una lista de valores, puede modificar de forma significativa la estructura del informe sin ningún costo añadido sobre el rendimiento.
Una desventaja de la función IndexCol es que no se puede utilizar con like en cálculos de entero, aunque se puede utilizar like en la lista de expresiones. Si un cálculo de entero requiere un like, debe utilizar una sentencia CASE en su lugar.
Ejemplo
Suponga que hay una variable de sesión denominada PREFERRED_CURRENCY que defina la moneda preferida para un usuario. Además, según el valor de la variable de sesión, , se muestran los ingresos en la moneda especificada por el usuario.
Se han creado dos cálculos para devolver la moneda correcta según el valor de la variable de sesión.
El primero utiliza una sentencia CASE como se indica a continuación:
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
ENDEl segundo utiliza la función IndexCol como se indica a continuación:
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")Debido a que el primer argumento de la función IndexCol se debe resolver en un entero, se utiliza una sentencia CASE para la resolución.
Cuando se ejecuta una consulta utilizando el cálculo de la sentencia CASE, la sentencia CASE completa se envía a la base de datos, dado que la sentencia CASE se evalúa en tiempo de ejecución. En algunos casos, esto genera incidencias con el optimizador.
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 ) D1Libros de trabajo e IndexCol
Puede utilizar la función IndexCol en libros de trabajo.
En este ejemplo, la función IndexCol se utiliza para cambiar la granularidad del período en una visualización:
- Cree un parámetro para utilizarlo como selector de columnas para seleccionar el detalle del período; en este caso Mes o Trimestre.
- Cree un cálculo personalizado para realizar la función
IndexCol. Este cálculo es: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") - Agregue el parámetro a la barra de filtros de un libro de trabajo. A continuación, los usuarios pueden cambiar la granularidad de un informe seleccionando Mes o Trimestre en el filtro del selector de columnas.
