|
Replies:
14
-
Pages:
1
-
Last Post:
Jan 8, 2008 10:52 AM
Last Post By: Tubby
|
|
|
Posts:
254
Registered:
06/19/00
|
|
|
|
Strange ORDER BY results
Posted:
Jan 8, 2008 9:59 AM
|
|
|
|
I have the following queries that do not produce the same sort results. The only difference between the 2 is the 'then' after the first case statement. Can anyone explain why? (I have highlighted the 'O' only because it is a variable that already has the value substituted)
select order_no, order_suffix, order_date, ship_complete_only, estship_dt, crdr_code
from oehead a
where a.order_status = 'O'
order by case
when 'O' = 'O' then 1
when 'O' = 'D' then 3
when 'O' = 'S' then 4
else 1
end;
select order_no, order_suffix, order_date, ship_complete_only, estship_dt, crdr_code
from oehead a
where a.order_status = 'O'
order by case
when 'O' = 'O' then order_no
when 'O' = 'D' then 3
when 'O' = 'S' then 4
else 1
end;
|
|
|
Posts:
2,272
Registered:
10/01/01
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:05 AM
in response to: sdortch
|
|
|
|
You have 2 NON-identical ORDER BY clauses.
Why would you expect them to be identical in results?
Maybe i'm confused and you showing some data would help me better explain the results to you, but based on your question and the information contained within, that's my answer.
Message was edited by:
Tubby
|
|
|
Posts:
986
Registered:
04/14/07
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:08 AM
in response to: sdortch
|
|
|
in the first case
1 would not be a column number in the select clause, but only a constant
so you are ordering by the constant - no ordering at all for me
VB
http://volder-notes.blogspot.com/ |
|
|
Posts:
254
Registered:
06/19/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:09 AM
in response to: Tubby
|
|
|
|
Thank you for your response.
In the first query the 'then 1' is a positional indicator that should reference the first column (order_no).
I believe these should create the same results.
|
|
|
Posts:
254
Registered:
06/19/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:13 AM
in response to: Volder
|
|
|
|
I guess I am confused as to why this is not a column number (positional indicator) when in a case statement within the order by but it is when it is not in a case statement. In other words
select order_no from oehead h
order by 1
works but my query referenced above does not
|
|
|
Posts:
2,272
Registered:
10/01/01
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:15 AM
in response to: sdortch
|
|
|
|
A desc of your table and the output you're getting should be enough to give you an answer.
|
|
|
Posts:
4,899
Registered:
11/22/02
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:18 AM
in response to: sdortch
|
|
|
select order_no, order_suffix, order_date, ship_complete_only, estship_dt, crdr_code
from oehead a
where a.order_status = 'O'
order by case when 'O' = 'O' then 1
when 'O' = 'D' then 3
when 'O' = 'S' then 4
else 1
end;
most probably because in the first query returns a literal string of 1 at the CASE statement. |
|
|
Posts:
3,437
Registered:
07/17/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:19 AM
in response to: sdortch
|
|
|
In the first query the 'then 1' is a positional indicator that should reference the first column (order_no).
Once again, ORDER BY allows either positional reference or expression. You can not use a combination of both. Therefore 1 in
ORDER BY 1
will be interpreted as ORDER BY first element in select list, while
ORDER BY 1+0
will order by expression 1+0 and not by first element in select list.
SY. |
|
|
Posts:
254
Registered:
06/19/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:30 AM
in response to: Solomon Yakobson
|
|
|
Below is a test example to help explain better. (first 11 records only shown) As you can see the order by 1 works but the case when xxx then 1 does not.
Tubby -- Below is as you requested
Sy -- I guess I do not understand why you feel that inside the case statement the 1 is an expression?!?!? Is it by definition somewhere that the results of a case statement are an expression?
SQL> desc test;
Name Null? Type
--------
ORDER_NO VARCHAR2(10)
ORDER_SUFFIX VARCHAR2(10)
ORDER_DATE DATE
SHIP_DATE DATE
SQL> select order_no from test order by case when ship_date is null then 1 end;
ORDER_NO
18396
18395
18397
18400
18398
18399
18443
18442
18444
18445
18394
SQL> select order_no from test order by case when ship_date is null then order_no end;
ORDER_NO
16034
16035
16036
16037
16038
16039
16040
16041
16042
16043
16044
SQL> select order_no from test order by 1;
ORDER_NO
16034
16035
16036
16037
16038
16039
16040
16041
16042
16043
16044 |
|
|
Posts:
4,899
Registered:
11/22/02
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:38 AM
in response to: sdortch
|
|
|
1. SQL> select order_no from test order by case when ship_date is null then 1 end;
2. SQL> select order_no from test order by case when ship_date is null then order_no end;
3. SQL> select order_no from test order by 1;
first query did not produce the expected result. second and third did produce the expected result. if you will examine the first query it returns the 1 which should have been practically understood and converted as the first column which is the order number. however it did not, the assumption was that most probably because the resultant of the case returns a literal string/value of 1. |
|
|
Posts:
1,167
Registered:
03/13/07
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:41 AM
in response to: sdortch
|
|
|
|
sdortch,
the case statement itself is an expression and its then clauses are expressions defining it's value when a given condition is satisfied. As such the line:
when 'O' = 'O' then 1
returns the integer value 1 and not the value at the ordinal column position 1.
The moment you put anything in your order by clause other than an ordinal number you have an expression on your hands and can't expect an ordinal number to work for that portion of your order by clause. You can however mix and match ordinal position numbers and expressions when they are separated by columns
e.g.:
order by <ordinal>, <expression>, <ordinal>, ...
|
|
|
Posts:
3,437
Registered:
07/17/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:43 AM
in response to: sdortch
|
|
|
Sy -- I guess I do not understand why you feel that inside the case statement the 1 is an expression?!?!?
CASE is expression. So when Oracle sees CASE it already knows it is ORDER BY expression.
SY. |
|
|
Posts:
4,899
Registered:
11/22/02
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:47 AM
in response to: sdortch
|
|
|
select order_no
from test
order by (case when ship_date is null then 1 end); <-- returns the resultant 1 expressed as literal value and not as column
|
|
|
Posts:
254
Registered:
06/19/00
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:51 AM
in response to: Sentinel
|
|
|
|
Thank you for the in depth answer(s).
|
|
|
Posts:
2,272
Registered:
10/01/01
|
|
|
|
Re: Strange ORDER BY results
Posted:
Jan 8, 2008 10:52 AM
in response to: sdortch
|
|
|
Thanks.
As i suspected, your column is a varchar and not a number.
ME_XE?create table explainer_num (column1 number);
Table created.
Elapsed: 00:00:00.25
ME_XE?create table explainer_chr (column1 varchar2(10));
Table created.
Elapsed: 00:00:00.25
ME_XE?
ME_XE?insert into explainer_num (select level from dual connect by level <=10)
10 rows created.
Elapsed: 00:00:00.23
ME_XE?insert into explainer_chr (select level from dual connect by level <=10)
10 rows created.
Elapsed: 00:00:00.21
ME_XE?
ME_XE?
ME_XE?select *
2 from explainer_num
3 order by
4 case
5 when column1 = 5
6 then
7 0
8 else
9 column1
10 end;
COLUMN1
5
1
2
3
4
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.34
ME_XE?
ME_XE?
ME_XE?select *
2 from explainer_chr
3 order by
4 case
5 when column1 = '5'
6 then
7 '0'
8 else
9 column1
10 end;
COLUMN1
5
1
10
2
3
4
6
7
8
9
10 rows selected.
Elapsed: 00:00:00.34
ME_XE?
ME_XE?select *
2 from explainer_chr
3 order by
4 case
5 when column1 = '5'
6 then
7 to_number('0')
8 else
9 to_number(column1)
10 end;
COLUMN1
5
1
2
3
4
6
7
8
9
10
10 rows selected.
Elapsed: 00:00:00.34
ME_XE?
|
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
