0

I'm creating a PDF report with a requirement that the generated report be sorted in the same order as the array of ids passed in, ids = [3, 2, 5, 1, 4]

I have found this previous question ORDER BY the IN value list, but haven't managed to get it to work

The following is part of my query

WHERE
    $X{IN, o.id, ids}
ORDER BY idx(ids, o.id)

Any suggestions?

Community
  • 1
  • 1
Carlos
  • 938
  • 7
  • 12
  • Quote from the answer: "*Don't forget to create the `idx` function first, as described here: http://wiki.postgresql.org/wiki/Array_Index*" –  Jul 11 '16 at 12:28
  • What exactly is the data type of `ids`? And what is your Postgres version? –  Jul 11 '16 at 12:29
  • Data types of ids is integer I forgot to create the idx function, even after creating it still doesn't work :( The following is the Jasperserver log file output of part of the query that it runs WHERE o.id IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ORDER BY idx(ids, o.id) – Carlos Jul 11 '16 at 13:04
  • This means that Jasper doesn't pass an array of ids, but each ID separately. In that case you won't be able to achieve what you want. You need to convince Jasper to pass a **single** parameter that is an array. –  Jul 11 '16 at 13:10

1 Answers1

0

Thanks to a_horse_with_no_name

Changing the query as per below solved the issue, 

ids = "3, 2, 5, 1, 4"

WHERE
    o.id in (ids)
ORDER BY idx(array[ids], o.id)

Also needed to create the idx function

Carlos
  • 938
  • 7
  • 12