Conditional sorting with SQL
These examples are in RPGIV but will work with any language free format if you take out the C-specs.
In both examples, host variables are used to control the sort. Host variable SortOption controls the sort in the first example. If host variable SortOption has a value of 1, the data is retrieved
in order by customer name. If two customers have the same name, theyare retrieved incustomer number sequence. If SortOption has any other value, the data is retrieved in customer number sequence.
D SortOption S 1P 0
C/exec sql
C+
C+ Declare C1 Cursor for
C+ select cusnum, lstnam, init
C+ from qiws/qcustcdt
C+ order by
C+ case when :SortOption = 1
C+ then lstnam
C+ else digits(cusnum) end,
C+ case when :SortOption = 1
C+ then cusnum
C+ else 0 end
C+
C/end-exec
In the second example, the data may be sorted by state, in either ascending or descending sequence. If host variable SortSeq has a value of A, the data is returned in ascending order by state. Within state, the data is sorted by last name and initials. A SortSeq value of D causes the states to be sorted in descending order, but the last name and initial fields are sorted in ascending order. If SortSeq has some other value, state is not used for sorting.
D SortSeq S 1A
C/exec sql
C+
C+ Declare C1 Cursor for
C+ select state, lstnam, init from qiws/qcustcdt
C+ order by
C+ case when :SortSeq = 'A' then state else ' ' end,
C+ case when :SortSeq = 'D' then state else ' ' end desc,
C+ lstnam,
C+ init
C+
C/end-exec
Or just:
select custnbr, custname, ccity, cstate
from customer
order by
case cstate when 'OH' then 0 else 1 end,
cstate, ccity
The first sort field is a case expression. For records that have a state value of OH, this sort field is zero. For all others, this sort field is one. Ohio records sort ahead of all others. Since you want other states in alphabetical order, I placed the CSTATE field as the second sort field. I didn't know your sort preference within state, so for the third sort field I chose CCITY. The system produces a record set like this one:
Customer
account Customer
number name City State
3 Army Surplus Brunswick OH
30003 Snowman Heat & Air Lexington OH
56 Jak's Liver Emporium Mexico OH
1 48% of Nothing Klondike AZ
222 Sardine Paradise Happah Palloolah CA
2 Robert R. Roberts IV New Yolk CA
3 Donaldson Electric Whittier CA
44 Joe's Shoes Duluthe GA
3 Sal Monella Dienstag MO
10001 Sue's Bridle Shop Saltillo MS
111 Ames Wholesale Tupelo MS
40004 Grayson Paul Little City ND
345 Gretta's Gifts Saddle Brook NJ
44 Ben Dover Jaxon Whole NV