Hello I'm trying to have some control on how my data is ordered
depending on an input parameter


my question is

in a stored procedure how can I do something like this at the end of my
statement.
pOrder as input value where pOrder can be 1 or 0

CASE WHEN pOrder = 1
THEN
ORDER BY STREET
ELSE
ORDER BY CITY
END

In article <1148574304.918404.217680@i39g2000cwa.googlegroups .com>,
elliottjeff@gmail.com says...
> Hello I'm trying to have some control on how my data is ordered
> depending on an input parameter
>
>
> my question is
>
> in a stored procedure how can I do something like this at the end of my
> statement.
> pOrder as input value where pOrder can be 1 or 0
>
> CASE WHEN pOrder = 1
> THEN
> ORDER BY STREET
> ELSE
> ORDER BY CITY
> END
>
>

I guess you need to think the other way around. Add a column to your
select statement which using the case clause will be filled with STREET
or CITY and order on that column.

select case when porder = 1 then street else city end, .....
order by 1

mandible wrote:
> Hello I'm trying to have some control on how my data is ordered
> depending on an input parameter
>
>
> my question is
>
> in a stored procedure how can I do something like this at the end of my
> statement.
> pOrder as input value where pOrder can be 1 or 0
>
> CASE WHEN pOrder = 1
> THEN
> ORDER BY STREET
> ELSE
> ORDER BY CITY
> END
>
If neither column is indexed anyway then you could do:
ORDER BY CASE WHEN pOrder = 1 THEN address ELSE city END

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Thank you serge that looks quite perfect to what I"m trying to
accomplish.

You missed the point thaty CASE is an expression and NOT a procedural
control statement.

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.

But aside from this, there is the good programming practice of showing
the fields that are used for the sort to the user, usually on the left
side of each line since we read left to right.

The standard trick for picking a sorting order at run time is to use a
flag in CASE expression. If you want to sort on more than one column
and allow all possible combinations of sorting use one CASE per column:

SELECT
CASE @flag_1
WHEN 'a' THEN CAST (a AS CHAR(n))
WHEN 'b' THEN CAST (b AS CHAR(n))
WHEN 'c' THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1,
CASE @flag_2
WHEN 'x' THEN CAST (x AS CHAR(n))
WHEN 'y' THEN CAST (y AS CHAR(n))
WHEN 'z' THEN CAST (z AS CHAR(n))
ELSE NULL END AS sort_2,
...
CASE @flag_n
WHEN 'n1' THEN CAST (n1 AS CHAR(n))
WHEN 'n2' THEN CAST (n2 AS CHAR(n))
WHEN 'n3' THEN CAST (n3 AS CHAR(n))
ELSE NULL END AS sort_2,

FROM Foobar
WHERE ...
ORDER BY sort_1, sort_2, ...

More than one sort column and only a limited set of combinations then
use concatenation.

CASE @flag_1
WHEN 'ab'
THEN CAST(a AS CHAR(n)) ||' ' || CAST(b AS CHAR(n))
WHEN 'ba'
THEN CAST(b AS CHAR(n)) ||' ' || CAST(a AS CHAR(n))
ELSE NULL END AS sort_1,

If you need ASC and DESC options, then use a combination of CASE and
ORDER BY

CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'ASC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'ASC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'ASC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_a,
CASE @flag_1
WHEN @flag_1 = 'a' AND @flag_1_ad = 'DESC'
THEN CAST (a AS CHAR(n))
WHEN @flag_1 = 'b' AND @flag_1_ad = 'DESC'
THEN CAST (b AS CHAR(n))
WHEN @flag_1 = 'c' AND @flag_1_ad = 'DESC'
THEN CAST (c AS CHAR(n))
ELSE NULL END AS sort_1_d

.. ORDER BY sort_1_a ASC, sort_1_d DESC

I have shown explicit CAST(<exp> AS CHAR(n)), but if the datatypes of
the THEN clause expressions were already the same, there would be no
reason to force the conversions.

You change the ELSE NULL clause to any constant of the appropriate
datatype, but it should be something useful to the reader.

A neater way of doing this is to use one column for each sorting option
so you do not have worry about CAST() operations.

SELECT ...
CASE WHEN @flag = 'a' THEN a ELSE NULL END AS sort1,
CASE WHEN @flag = 'b' THEN b ELSE NULL END AS sort2,
CASE WHEN @flag = 'c' THEN c ELSE NULL END AS sort3
FROM Foobar
WHERE ...
ORDER BY sort1, sort2, sort3;

--CELKO-- wrote:
> CASE WHEN @flag = 'c' THEN c ELSE NULL END AS sort3
> FROM Foobar
> WHERE ...
> ORDER BY sort1, sort2, sort3;
Joe,

Are you sure referencing to the correlation name in the ORDER BY clause
is SQL-92? That's teh first I hear of it.

Cheers
Serge

PS: Contrary to the belief of some the US Government has limited say in
this _world_wide_ newsgroup. ;-)



--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

>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.

willyunger@gmail.com wrote:
> >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.

yes serge's idea did exactly what I was looking for.