Search code examples
javasqlpostgresql

How to insert sort direction into SQL query dynamically

I have this at the end of my SQL query:

ORDER BY
    CASE
        WHEN :sortField = 'creationDate' THEN update_.creation_date
        ELSE update_.default_time_field
        END,
    CASE WHEN :sortDirection = 'DESC' THEN DESC END;

However, this gives me a compile error (the 'DESC' refers to the DESC at the very end. Not the strings before it):

<expression> expected, got 'DESC'

Which is fine, but I'm having a hard time going around it. SQL just won't let me.

I am using Postgres 11. I am injecting parameters using Java code (Micronaut-Data. No, I cannot use Pageable).

Here is how the above looks when I actually enter the values for the variables:

ORDER BY
    CASE
        WHEN 'creationDate' = 'creationDate' THEN update_.creation_date
        ELSE update_.default_time_field
        END,
    CASE WHEN 'DESC' = 'DESC' THEN DESC END;
Solution

  • Answer based on @dale-k's comment:

    ORDER BY
  CASE WHEN :sortField = 'creationDate' AND :sortDirection = 'DESC' THEN update_.creation_date END DESC,
  CASE WHEN :sortField = 'creationDate' AND :sortDirection = 'ASC' THEN update_.creation_date END ASC,
  CASE WHEN :sortField is NULL THEN update_.default_time_field END DESC;