Pentaho

 View Only

 Pentaho CDE SQL Query with numerical param

  • Pentaho
  • Pentaho
Matias Panasci's profile image
Matias Panasci posted 02-07-2018 11:42

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.


#Pentaho
Attachment  View in library
Pentaho CDE SQL Query with numerical param 3 KB
Carlos Russo's profile image
Carlos Russo posted 02-07-2018 22:46

You'll need to post the server logs here (e.g. pentaho.log and eventually catalina.out) to pinpoint the culprit.

Without that information, I can only try to guess. My hunch is that you messed up the configuration of the "parameters" property of the component that consumes the datasource containing that query.

Usually you need to keep in mind that:

  • in the datasource definition, the property "parameters" declares the datasource parameters (paramValue,paramAnio, etc )
  • in the component, the property "parameters" allows us to associate/map dashboard's parameters to datasource parameters (I'm guessing you skipped this step)
Ana Gonzalez's profile image
Ana Gonzalez posted 02-08-2018 09:44

Just an observation, I don't know your database, but normally from performance point of view, to take advantage of indexes the tables might have, it's better to not use functions on columns if you can avoid it, and use the functions on parameters:

WHERE   fact_Ventas.idAnio = CAST(${paramAnio} AS INTEGER) AND  fact_Ventas.idMes = CAST(${paramMes} AS INTEGER) AND  fact_Ventas.idVendedor  = CAST(${paramVendedores} AS INTEGER) AND  fact_Ventas.origen  = CAST(${paramOrigen} AS INTEGER)

As I don't know your data model it might not be posible and you have your reasons to write the query as you have chosen, but just in case, I had to point it out.

Regards

Matias Panasci's profile image
Matias Panasci posted 02-08-2018 10:49

Carlos, thanks for your quick response, later I will review the log and I will post it so you can help me.

Thank you very much for your time, you are very kind.

Matias Panasci's profile image
Matias Panasci posted 02-08-2018 10:51

Ana, thank you very much for your advice, I will put it into practice as soon as possible. This type of information helps me a lot, I am quite new to these issues.

Very nice of you.

Data Conversion's profile image
Data Conversion posted 02-08-2018 15:34

The way you implement the dashboard is important to how the parameters are mapped from the dashboard to the query. For example, if you are using a simple parameter, a table component and an SQL query, you will need to make the table component map the dashboard parameter with the query parameter, because it's the table that triggers the query sending the query parameter with its value to CDA to execute the query.

Attached you can find a sample dashboard that you need can adapt to your database table.

Data Conversion's profile image
Data Conversion posted 08-15-2019 00:17
Attachment  View in library
sample.zip 3 KB

Related Content