Query rule conditional sort on multiple columns

Question

Hello, 
 is it possible to have a conditional sort on multiple columns in a query rule? I need to sort the data by modified date and created date columns. Modified date might be null for some rows. Below order by expression gives just what i want when i use it with the SQL query. Is there a way of creating same logic in a query rule? 
 order by case when MODIFIED_DATE is null then CREATED_DATE else MODIFIED_DATE end DESC 
 I tried below code but it didn't help. 
 pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1, sort: {a!sortInfo(field: "createdDate", ascending: false), a!sortInfo(field: "modifiedDate", ascending: false)} )

Mike Schmitt · Answer

emineo0001 said: order by case when MODIFIED_DATE is null then CREATED_DATE else MODIFIED_DATE end DESC 
 If you claim this works in SQL then I'll take your word for it, but inherently it really doesn't make much sense. ORDER BY is meant to apply across all rows of a search result set - it logically breaks if you try to pick and choose which results within that set it applies to. 
 I second Stefan's suggestion to write a View where you establish a bespoke "sort date" column, which can (on a row-by-row basis) establish the value for that new column based on whether the "created date" actually exists, and if not, fill it with the "modified date" instead. From Appian's end it'll then be fairly straightforward to sort a query result by the value of that column.

Peter Lewis · Answer

If you're using a synced record type, this is also a great scenario for using a custom record field - just add a field that uses the a!defaultValue() function and returns the CREATED_DATE if MODIFIED_DATE is null. Then you can sort directly on that custom record field to achieve the result you're looking for.