Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: multiple values for select where in not working

  1. #1
    Join Date
    Jul 2006
    Posts
    7

    Default multiple values for select where in not working

    Hi,

    I'm trying to set up a report which select several records in a select with where in (${parameter}). To do that the parameters will be received as string and converted using CSVARRAY. Thats works and I can see that the parameters are passed to database query but I always get an error at postgres saying:

    ERROR: operator does not exist: bigint = character varying at character 6475
    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

    The config is as follow:

    parameter1 is a string an receive the multiple value list coma separated: 1401,2270,2271,1886,1695,1799,1770,1666,1194,1190,1187,1183,1180,1143,1142,1129,1130

    parameter2 is defined as object, hidden, with postprocesing formula=CSVARRAY([parameter1];0;","

    At query definition we have:
    select bla,bla from tablename where id in (${parameter2})

    And when we run the report on the PRD gui to test it we always get the error:
    ERROR: operator does not exist: bigint = character varying at character 6475
    HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.


    Any ideas?

    Thanks!!

    Edit: We are using postgresql 9.5 and PRD 7.0.0.0-25
    Last edited by echacuen; 09-08-2017 at 07:29 PM.

  2. #2
    Join Date
    May 2014
    Posts
    358

    Default

    I haven't tried this specifically on Postgres, but I think you have to define the parameter as a multivalue list, then maybe hide it from the user and fill it using a MultiValueQuery formula?

  3. #3
    Join Date
    Aug 2016
    Posts
    142

    Default

    I think the error is that the query is expecting a list of integers but is receiving a string

    I found the CSVARRAY trick for multivalue parameters to be very slow so I use query scripting instead

    Go to query scripting and select Groovy as the language. Paste the below and change the parameter1 to whatever your parameter is called. I also usually give the parameter a default value.

    Code:
    def computeQuery (query, queryName, dataRow)
    {
         query = query.replaceAll("FILTER id", dataRow.get("parameter1").toString())
         return query
    }
    Then in your query modify it so that it is something like this

    id IN (FILTER id)

    It should now work for you

    The only downside to this approach that it will only accept a limited number of values. What that limit is depends on how many characters your query is and how many digits your id values are. It is limited by the max URL length set by your browser/server as the query is passed over the URL
    Last edited by Deanicus; 09-11-2017 at 05:18 AM.

  4. #4
    Join Date
    Jul 2006
    Posts
    7

    Default

    Deanicus is right. We are using that report from a web app and the parameters must by passed using the url.

    Thanks for the point. I'll try this afternoon and let you know if it works.

  5. #5
    Join Date
    Jul 2006
    Posts
    7

    Default

    Sorry for the delay, but suddenly we had another priorities, so this one was delayed. Now we are coming back to this.

    The solution look nice, but we are using some javascript already to change the datasaource because we are using different databases... is there any way to the the same on javascript?

    Thanks!!

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.