Custom Sorting with Decode
When trying to sort based on values that do not fit the standard ascending and descending sort logic provide by your database engine you can use the following decode() function to sort based on custom value ordering.
By ordering your value list in a decode function you can sort based on the integer values you assign to each value.
SELECT X.FIELDVALUE, X.XLATLONGNAME
FROM PSXLATITEM X
WHERE X.FIELDNAME = 'NOR_IMPRINT_TYPE'
ORDER BY decode( X.FIELDVALUE, 'S', 1, 'N', 2, 'O', 3 )
The result is then:
| FIELDVALUE | XLATLONGNAME |
|---|---|
| S | Standard |
| N | Optional |
| O | Optional, additional charge |
No Comments