>Standard SQL-92 does not allow you to use a function or expression in
an ORDER BY clause. The ORDER BY clause is part of a cursor and it can
only see the column names that appear in the SELECT clause list that
was used to build the result set. Someone will now chime in that
SQL-99 (officially called "a standard in progress" and not recognized
by the U.S. Government for actual use) does allow this.
Hi,
Without getting into which standard allows what, nor diplomatic
controversies about whether the U.S. Government acknowledges it or not,
I would like to add that Serge's suggestion also works with DECLAREd
temp tables, at least in DB2 for LUW FP12.
Cheers,
Willy
(example follows)
declare global temporary table control (code smallint) on commit
preserve rows
insert into willy.test values
(0,'john','elm drive'),
(1,'mary','mill crescent'),
(2,'peter', 'charing cross avenue'),
(3,'paula','beaufort street')
"select * from willy.test order by case when (select code from
session.control fetch first row only) = 0 then name else address end"
CODE NAME ADDRESS
------ ------------------------------ ------------------------------
3 paula beaufort street
2 peter charing cross avenue
0 john elm drive
1 mary mill crescent
4 record(s) selected.
"select * from willy.test order by case when (select code from
session.control fetch first row only) = 1 then name else address end"
CODE NAME ADDRESS
------ ------------------------------ ------------------------------
0 john elm drive
1 mary mill crescent
3 paula beaufort street
2 peter charing cross avenue
4 record(s) selected.