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.XLATLONGNAMEFROM PSXLATITEM XWHERE X.FIELDNAME = 'NOR_IMPRINT_TYPE' ORDER BYdecode( X.FIELDVALUE, 'S', 1, 'N', 2, 'O', 3 )
| FIELDVALUE | XLATLONGNAME |
|---|---|
| S | Standard |
| N | Optional |
| O | Optional, additional charge |