# Thread: case with order by

1. Registered User
Join Date
Sep 2011
Posts
220

## Unanswered: case with order by

Hi;

The below query should execute based on the value of SORT variable in the ORDER BY clause and produce the result sets like ASCENDING or DESCENDING order

DB2 9.1 with z/OS

Code:
```SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des

FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=111

union
SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des
FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=222

order by

case  when :sort='A-1' THEN t1.p_la
END ASC
,CASE
WHEN :sort='A-2' THEN t1.p1_nn
END ASC
,t1.p2_nn
,case
WHEN :sort='A-3' THEN t2.t_des
end asc
,case

when :sort='D-1' THEN t1.p_la
END DESC
,CASE
WHEN :sort='D-2' THEN t1.p1_nn
END DESC
,t1.p2_nn
,case
WHEN :sort='D-3' THEN t2.t_des
end DESC```
Code:
```Table1

p_la   p1_Nn    p2_nn          p_dD
B11     12       34            aaa
A11     30      334            aaa

Table2

t_no   p1_Nn    p2_nn       T_DES
111      12       34        XXX
222      30      334        SSS```
Expected result

when the ORT ='A-1' result set will be in ASC order of t1.p_la
when the ORT ='A-2' result set will be in ASC order of t1.p1_nn,t1.p2_nn

when the ORT ='D-1' result set will be in DESC order of t1.p_la
when the ORT ='D-2' result set will be in DESC order of t1.p1_nn,t1.p2_nn

But i am facing error like

SQL0214N An expression in the ORDER BY clause in the following position, or
starting with "1" in the "ORDER BY" clause is not valid. Reason code = "1".
SQLSTATE=42822
Please help

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
SQL0214N Reason code = "1".

DB2 Version 9.1 for z/OS　Codes
1 The fullselect of the select-statement is not a
subselect. Expressions are not allowed in the
ORDER BY clause for this type of
select-statement. This reason code occurs only
when clause-type is ORDER BY.
Try to enclose by an outer select statement, like...
SELECT *
FROM (
/* your query without order by clause */
) AS s
/* your order by clause with removing all column qualifiers("t1." and "t2.") */

3. Registered User
Join Date
Sep 2011
Posts
220
Thanks for the help,working fine...

4. Registered User
Join Date
Sep 2011
Posts
220
Hi;

I was executed the below query,but the DESC order not returning the proper values..

Code:
```select
p_la
, p1_nn
, p2_nn
, p_dd
,t_des
from(

SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des

FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=111

union
SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des
FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=222
) as s

order by

case  when :sort='A-1' THEN p_la
END ASC
,CASE
WHEN :sort='A-2' THEN p1_nn
END ASC
,p2_nn
,case
WHEN :sort='A-3' THEN t_des
end asc
,case

when :sort='D-1' THEN p_la
END DESC
,CASE
WHEN :sort='D-2' THEN p1_nn
END DESC
,p2_nn
,case
WHEN :sort='D-3' THEN t_des
end DESC```
Please help..

5. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
There was ",p2_nn" before ",case WHEN :sort='A-3' ..." and DESC expressions in ORDER BY clause.

So, If :sort was not 'A-1' nor 'A-2', then results would be ordered by p2_nn, even if :sort was 'A-3'.
Last edited by tonkuma; 03-09-12 at 14:52. Reason: Add "case WHEN :sort='A-3' ..."

6. Registered User
Join Date
Jul 2009
Location
NY
Posts
963
It is better to create the Dynamic Query then looking for solution for this one.

You'll have the constant character string part and just add to this part string with order by depending on host variable "sort".

It will work.

Lenny

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Originally Posted by Billa007
Hi;

I was executed the below query,but the DESC order not returning the proper values..

Code:
```select
p_la
, p1_nn
, p2_nn
, p_dd
,t_des
from(

SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des

FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=111

union
SELECT t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des
FROM
table1 t1
inner join
table t2
on
t1.p_la=t2.p_la
and t1.p1_nn=t2.p1_nn
and t1.p2_nn=t2.p2_nn
where
t2.t_no=222
) as s

order by

case  when :sort='A-1' THEN p_la
END ASC
,CASE
WHEN :sort='A-2' THEN p1_nn
END ASC
,p2_nn
,case
WHEN :sort='A-3' THEN t_des
end asc
,case

when :sort='D-1' THEN p_la
END DESC
,CASE
WHEN :sort='D-2' THEN p1_nn
END DESC
,p2_nn
,case
WHEN :sort='D-3' THEN t_des
end DESC```
Sorry, I lost your original requirements.
Expected result

when the :SORT ='A-1' result set will be in ASC order of t1.p_la
when the :SORT ='A-2' result set will be in ASC order of t1.p1_nn,t1.p2_nn

when the :SORT ='D-1' result set will be in DESC order of t1.p_la
when the :SORT ='D-2' result set will be in DESC order of t1.p1_nn,t1.p2_nn
The code might be simplified like...
Replaced whole of ORDER BY clause.
Code:
```SELECT DISTINCT
t1.p_la
, t1.p1_nn
, t1.p2_nn
, t1.p_dd
, t2.t_des
FROM  table1 t1
INNER JOIN
table  t2
ON   t1.p_la  = t2.p_la
AND t1.p1_nn = t2.p1_nn
AND t1.p2_nn = t2.p2_nn
WHERE t2.t_no IN (111 , 222)
ORDER BY
CASE :sort
WHEN 'A-1' THEN
p_la
WHEN 'A-2' THEN
p1_nn
WHEN 'A-3' THEN
t_des
END  ASC
, CASE :sort
WHEN 'A-2' THEN
p2_nn
END  ASC
, CASE :sort
WHEN 'D-1' THEN
p_la
WHEN 'D-2' THEN
p1_nn
WHEN 'D-3' THEN
t_des
END  DESC
, CASE :sort
WHEN 'D-2' THEN
p2_nn
END  DESC
;```
Last edited by tonkuma; 03-10-12 at 00:06. Reason: Check "Disable smilies in text". Add two additional cases('A-3' and 'D-3'). Replace whole of ORDER BY clause.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•