Home » SQL & PL/SQL » SQL & PL/SQL » Irregular nature of Order By Clause (Oracle 9i/10g)
| Irregular nature of Order By Clause [message #318487] |
Wed, 07 May 2008 00:47  |
rajat_chaudhary
Messages: 108
Registered: November 2006
Location: india |
Senior Member |
 
|
|
Hi experts,
in below pasted code :- 2 tables, 2 Sql Statements.
In which One is running fine and the other shows the irregular behaviour of Order By Clause.
create table test
(a number,b number, c number, d number, e number,f number,g number);
create table test2
(a number);
-- it gives error
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
Order By Case 7 when 25 Then '1'
When 26 Then '2 '
When 27 Then '3'
When 28 Then '4'
When 28 Then '5'
Else 6 End, PP.c desc;
-- it works
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
Order By 7;
Please make me confirm that why the above defined SQL Statement not works with the Order By having CASE .
Thanks in advance
|
|
|
| Re: Irregular nature of Order By Clause [message #318507 is a reply to message #318487 ] |
Wed, 07 May 2008 01:12  |
Michel Cadot
Messages: 15238
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
I confirm it is not valid.
7 is always 7 and never 25, 26, 27, 28, 28(!) or 6.
Regards
Michel
[Updated on: Wed, 07 May 2008 01:13]
|
|
|
| Re: Irregular nature of Order By Clause [message #318521 is a reply to message #318487 ] |
Wed, 07 May 2008 01:38 |
coleing
Messages: 92
Registered: February 2008 |
Member |
|
|
|
Yes, if you want to use CASE, you have to specify the column name in the order by clause, and not the position.
|
|
|
| Re: Irregular nature of Order By Clause [message #318544 is a reply to message #318521 ] |
Wed, 07 May 2008 02:43 |
rajat_chaudhary
Messages: 108
Registered: November 2006
Location: india |
Senior Member |
|
|
hi coleing
I agree , so the query is like
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
Order By Case PP.a when 25 Then '1'
When 26 Then '2 '
When 27 Then '3'
When 28 Then '4'
When 28 Then '5'
Else 6 End, PP.c desc;
but it is also not working.
Now give a solution , please.
|
|
|
| Re: Irregular nature of Order By Clause [message #318546 is a reply to message #318544 ] |
Wed, 07 May 2008 02:46 |
pablolee
Messages: 936
Registered: May 2007
Location: Scotland |
Senior Member |
|
|
| Quote: | but it is also not working.
|
Not a valid way of pointing out an issue. You must say WHY it is not working (error, unexpected results, no results et al)
Your issue can be resolved by placing your union queries in a subselect and doing an order by in the main query.
|
|
| |
| Re: Irregular nature of Order By Clause [message #318552 is a reply to message #318549 ] |
Wed, 07 May 2008 03:03 |
pablolee
Messages: 936
Registered: May 2007
Location: Scotland |
Senior Member |
|
|
| Quote: | can you please paste your suggested query ?
|
No, You give it a try i.e. put what you currently have (without the order by) in a subquery then in the main (outer) query use the order by.
| Quote: | and by the way whaz wrong to ask like that
|
Because
| Quote: | but it is also not working.
|
Provides absolutely no useful information and is therefore pointless. (By the way, the word is What's or What is NOT Whaz)
|
|
|
| Re: Irregular nature of Order By Clause [message #318555 is a reply to message #318549 ] |
Wed, 07 May 2008 03:10 |
JRowbottom
Messages: 2663
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
What is wrong with asking like that is that it gives us no information to work with.
If your code generates an error, it makes it much easier for us to help you if you actually tell us what the error was.
Now, fortunately for you, my crystal ball is back from the repair shop, and it tells me that your most likely error is 'ora-01785 : Order by item must be the number of a Select list item'
Basically, you can't do what you want to do at the level you're trying to do it.
Pablolee's advice is right - select everything from the current union query, and move the order by up to that outer query.
Once you've done that, you'll get an error about unexpected data types, as your CASE statment returns Varchar2s for some cases, and Numbers for others, and this is a bit of a no-no.
You posted this in the Expert forum, so you're obviously confident of your own SQL abilities, and we won't need to provide you with the actual query, will we?
|
|
|
| Re: Irregular nature of Order By Clause [message #318557 is a reply to message #318552 ] |
Wed, 07 May 2008 03:11 |
rleishman
Messages: 2563
Registered: October 2005
Location: Melbourne, Australia |
Senior Member |
|
|
When you have a UNION, your ORDER BY may only include column offsets (eg. 5 for the 5th column). Further, you cannot construct expressions around these offsets (CASE 5 WHEN ...).
You want to order by an expression
AND...
You want to use a UNION
THEREFORE...
Place the expression as the 8th column in each SELECT clause and use "ORDER BY 8"
Ross Leishman
|
|
|
| Re: Irregular nature of Order By Clause [message #318561 is a reply to message #318552 ] |
Wed, 07 May 2008 03:13 |
rajat_chaudhary
Messages: 108
Registered: November 2006
Location: india |
Senior Member |
|
|
OK fine so according to you the query is like :-
select * from
(Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2)
Order By Case PP.a when 25 Then '1'
When 26 Then '2 '
When 27 Then '3'
When 28 Then '4'
When 28 Then '5'
Else 6 End, PP.c desc;
but it is also not working and through the error i.e.
Else 6 End, PP.c desc
*
ERROR at line 19:
ORA-00904: "PP"."C": invalid identifier |
|
|
| Re: Irregular nature of Order By Clause [message #318564 is a reply to message #318561 ] |
Wed, 07 May 2008 03:18 |
JRowbottom
Messages: 2663
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
That is because the table alias PP is only valid in the queries that are being Unioned together.
At the level at which the Order By is being performed, you can't use that alias. Just use the column names instead.
You're still going to hit the 'Ora-00932 Inconsistent Datatypes' error though.
|
|
|
| Re: Irregular nature of Order By Clause [message #318568 is a reply to message #318564 ] |
Wed, 07 May 2008 03:26 |
rajat_chaudhary
Messages: 108
Registered: November 2006
Location: india |
Senior Member |
|
|
Now it's working and the code is :-
select * from (Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2
UNION
Select PP.a,PP.b,PP.c,PP.d,PP.e,PP.f,PP.g
from Test PP Join Test2 P
On PP.a = P.a
where PP.a = 2)
Order By Case when a = 25 Then '1'
When a = 26 Then '2 '
When a = 27 Then '3'
When a = 28 Then '4'
When a = 28 Then '5'
Else '6' End, c desc;
Thanks to all the experts.
|
|
|
| Re: Irregular nature of Order By Clause [message #318570 is a reply to message #318568 ] |
Wed, 07 May 2008 03:28 |
pablolee
Messages: 936
Registered: May 2007
Location: Scotland |
Senior Member |
|
|
The duplication of a = 28 is a bit daft:
When a = 28 Then '4'
When a = 28 Then '5' |
|
|
| Re: Irregular nature of Order By Clause [message #318572 is a reply to message #318570 ] |
Wed, 07 May 2008 03:29 |
JRowbottom
Messages: 2663
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
|
I'd upgrade 'daft' to 'buggy' personally.
|
|
| |
| Re: Irregular nature of Order By Clause [message #318580 is a reply to message #318574 ] |
Wed, 07 May 2008 03:48 |
JRowbottom
Messages: 2663
Registered: June 2006
Location: Sunny North Yorkshire, ho... |
Senior Member |
|
|
|
That's about as polite as it gets, too.
|
|
|
| Re: Irregular nature of Order By Clause [message #318581 is a reply to message #318580 ] |
Wed, 07 May 2008 03:51 |
pablolee
Messages: 936
Registered: May 2007
Location: Scotland |
Senior Member |
|
|
|
|
|
Goto Forum:
Current Time: Sat May 17 00:21:46 CDT 2008
Total time taken to generate the page: 0.31105 seconds |
]
Members
Search
Help
Register
Login
Home
