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 Go to next message
rajat_chaudhary
Messages: 141
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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]

Report message to a moderator

Re: Irregular nature of Order By Clause [message #318521 is a reply to message #318487] Wed, 07 May 2008 01:38 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior 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 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 #318549 is a reply to message #318546] Wed, 07 May 2008 02:55 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
Registered: November 2006
Location: india
Senior Member

hi pablolee,

can you please paste your suggested query ?

and by the way whaz wrong to ask like that
Re: Irregular nature of Order By Clause [message #318552 is a reply to message #318549] Wed, 07 May 2008 03:03 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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 Go to previous messageGo to next message
rajat_chaudhary
Messages: 141
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 Go to previous messageGo to next message
pablolee
Messages: 2836
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 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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 #318574 is a reply to message #318572] Wed, 07 May 2008 03:32 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
So that's your polite euphamism where you come from? Smile
Re: Irregular nature of Order By Clause [message #318580 is a reply to message #318574] Wed, 07 May 2008 03:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
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 Go to previous message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Smile
Previous Topic: File write error
Next Topic: Rewriting Query
Goto Forum:
  


Current Time: Fri Dec 09 19:19:46 CST 2016

Total time taken to generate the page: 0.10547 seconds