Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: CDE + Postgres Query + Parameter

  1. #1

    Question 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?

  2. #2
    Join Date
    Nov 2011
    Posts
    1,229

    Default

    What component is calling your query ? It should define param_period as one of its parameters as well.
    Pedro Vale
    --
    CTools Product Development
    http://www.webdetails.pt

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.