AnsweredAssumed Answered

Pentaho CDE SQL Query with numerical param

Question asked by Matias Panasci on Feb 7, 2018
Latest reply on Feb 8, 2018 by Andre Jorge

I would like to know if it is possible to perform an SQL query and pass it a numerical parameter. Let's suppose that I have the following query:

 

  SELECT
        CONCAT(cuatrimestre,' Cuatrimestre') As quarter
    ,   ROUND(SUM(fact_Ventas.cantidad * precioUnitario),0) as amount
    FROM fact_Ventas 
    INNER JOIN dim_Tiempos ON 
    fact_Ventas.idAnio = dim_Tiempos.idAnio AND 
    fact_Ventas.idMes = dim_Tiempos.idMes AND 
    fact_Ventas.idDia = dim_Tiempos.idDia
    INNER JOIN dim_Clientes ON dim_Clientes.idCliente = fact_Ventas.idCliente
    INNER JOIN dim_Productos ON dim_Productos.idProducto = fact_Ventas.idProducto
    WHERE 
        CAST(fact_Ventas.idAnio As Char) LIKE ${paramAnio} AND
        CAST(fact_Ventas.idMes As Char) LIKE ${paramMes} AND
        CAST(fact_Ventas.idVendedor As Char) LIKE ${paramVendedores} AND
        CAST(fact_Ventas.origen As Char) LIKE ${paramOrigen} AND
        dim_Productos.marca LIKE ${paramMarca} AND
        dim_Clientes.segmentoCliente LIKE ${paramSegmento} 
    GROUP BY 1
    ORDER BY 1 

I want to divide the column amount, by a numerical value extracted from a simple parameter. I managed to use filters in the where clause, but I can not make a division in a column.

 

Try the following query:

 

 SELECT
        CONCAT(cuatrimestre,' Cuatrimestre') As cuatrimestre
    ,   ROUND( (SUM(fact_Ventas.cantidad * precioUnitario)/${paramValue}),0) as Importe
    FROM fact_Ventas 
    INNER JOIN dim_Tiempos ON 
    fact_Ventas.idAnio = dim_Tiempos.idAnio AND 
    fact_Ventas.idMes = dim_Tiempos.idMes AND 
    fact_Ventas.idDia = dim_Tiempos.idDia
    INNER JOIN dim_Clientes ON dim_Clientes.idCliente = fact_Ventas.idCliente
    INNER JOIN dim_Productos ON dim_Productos.idProducto = fact_Ventas.idProducto
    WHERE 
        CAST(fact_Ventas.idAnio As Char) LIKE ${paramAnio} AND
        CAST(fact_Ventas.idMes As Char) LIKE ${paramMes} AND
        CAST(fact_Ventas.idVendedor As Char) LIKE ${paramVendedores} AND
        CAST(fact_Ventas.origen As Char) LIKE ${paramOrigen} AND
        dim_Productos.marca LIKE ${paramMarca} AND
        dim_Clientes.segmentoCliente LIKE ${paramSegmento} 
    GROUP BY 1
    ORDER BY 1 

But it gives an error and the data is not loaded. The syntax of the query was tested in the database and is correct.

Outcomes