multiple values for select where in not working

[echacuen]

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

[Lukfi]

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?

[Deanicus]

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.

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

[echacuen]

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.

[echacuen]

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!!