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?