Bind Variables in order by in OracleSql

Question

I have requirement to have different order by criteria that will be set by user from UI. e.g. it can be

order by a asc, b desc, c asc , d asc

or

order by b asc, a desc, c desc , d desc

It can be any combination that I will make from UI. How can I pass this to order by in OracleSql using Bind Variables in mulesoft 3.9

Answer 1

Assuming the column data types are compatible you can do something like this and repeat for as many sort options as needed, if the data types aren't compatible, split the case statements up to have one pair (asc,desc) for each group of compatible data types:

select * from your_table
 order by case case :col_1_dir when 'desc' then null else :col_1 end
             when 'a' then a
             when 'b' then b
             when 'c' then c
             when 'd' then d
             else null
          end
        , case case :col_1_dir when 'asc' then null else :col_1 end
             when 'a' then a
             when 'b' then b
             when 'c' then c
             when 'd' then d
             else null
          end desc
        , case case :col_2_dir when 'desc' then null else :col_2 end
             when 'a' then a
             when 'b' then b
             when 'c' then c
             when 'd' then d
             else null
          end
        , case case :col_2_dir when 'asc' then null else :col_2 end
             when 'a' then a
             when 'b' then b
             when 'c' then c
             when 'd' then d
             else null
          end desc