DB2

DB2 is IBM's offering to the highend database market. The latest version of DB2 (Universal Database) is ideal for OLTP, Data Warehousing, Decision Support and everything in between. It's well priced, extremely scalable and runs on virtually every platform out there from handhelds to mainframes.

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
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.") */
Thanks for the help,working fine...
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..
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'.
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
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
;

Related Links

COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0125E Function sequence error. SQ
problems with DB2 Administration server
st
Adding space to SMS tablespaces - alternatives?
db2inidb through API
How to obtain a lock on the table
Activate not logged initially issue
Page cleaners Tuning
PHP and db2diag command
Recursive query - Help needed...
DB2 Backups, Jobs from Tivoli
FP/DB Upgarade
Usage of LTRIM and RTRIM
DB2/VM and LONG VARCHAR
Physical disk layout and DB2 containers. How to config?
Connection Excption

Categories

DataBase Dev
MongoDB
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
Crystal Reports
Delphi, C etc
JAVA
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
Brilliant Database
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
New Members & Introduc...
Applications & Tools
Database Concepts & De...
Chit Chat
Marketplace
Job Opportunities
Suggestions & Feedback

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App