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)} )
Discussion posts and replies are publicly visible
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.