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.
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 primer argumento <<integer_literal>>
tiene tres valores posibles, debe haber tres argumentos en el argumento <<expr_list>>
, uno apra 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.
Beneficios de la función
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 puede utilizarla con "like" en cálculos de entero, aunque 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:
Supongamos que hay una variable de sesión denominada PREFERRED_CURRENCY
que define la moneda preferida para un usuario y, 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 en este ejemplo:
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
El segundo utiliza la función IndexCol
como en este ejemplo.
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 ) D1
Libros de trabajo e INDEXCOL
Con la llegada de los parámetros, ahora la función IndexCol
se puede utilizar en los libros de trabajo.
En este ejemplo, la función IndexCol
se utiliza para cambiar la granularidad del período en una visualizació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")