CDE + Postgres Query + Parameter
Hi, I managed to successfully use parameters to generate dynamic queries in simple cases.
But I'm stuck in more complex query.
I need to execute this query:
SELECT * FROM (
SELECT
date_trunc('day',date_sent)::date AS dia
, 'Notificações enviadas'::varchar AS tipo_registro
, count(id) AS quantidade
FROM mobile_marketing.notification_message nm
GROUP BY 1
) AS s
WHERE dia >= date_trunc('day',(NOW() - ? * interval '1 day' ))
Where the "?" is 7, 14 ,0 etc...
I'm using Pentaho BI CE 5.4
Postgres 9
I have a Select Component
Values Array:
Arg: 7, Value "Last 7 days"
Arg: 14, Value "Last 2 weeks"
Arg: 0, Value "Today"
Parameter: param_periodo
Value as id: False
Attempt 1:
SQL over SQLJNDI component:
Query:
...
WHERE dia >= date_trunc('day',(NOW() - ${param_periodo} * interval '1 day' ))
...
Parameter:
param_periodo , type Integer
ERROR: Grave: Mapped exception to response: 501
javax.ws.rs.WebApplicationException: java.lang.NumberFormatException: For input string: ""
Attempt 2:
SQL over SQLJNDI component:
Query:
...
WHERE dia >= date_trunc('day',(NOW() - (${param_periodo}|| 'days')::interval))
...
Parameter:
param_periodo , type String
ERROR: Grave: Mapped exception to response: 501
javax.ws.rs.WebApplicationException: pt.webdetails.cda.dataaccess.QueryException: ERROR: invalid input syntax for type interval: "days"
How to properly use the parameters in this type of query?
PLUS: How to make Pentaho server outuput a detailed log of these queries, how they were created and sent to Postgres?
