Search code examples
postgresqljasper-reports

Jasperserver PostgreSQL sort output

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?

Solution

  • 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