Re: order by/case expression problem

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 8 Dec 2001 11:47:01 -0800
Message-ID: <9utqnl01trr@drn.newsguy.com>

In article <pan.2001.12.08.13.07.27.721626.10604_at_earthlink.net>, Mladen says...
>
>On Sat, 08 Dec 2001 00:15:31 -0500, Cyber Office wrote:
>
>> The last select statement in the following batch seems to return
>> incorrect result.
>>
>> Am I right?
>>
>> --------------------------------------------------------------------------
>> drop table d
>> create table d (d char(10))
>> insert into d values ('1')
>> insert into d values ('10')
>> insert into d values ('9')
>>
>> select d from d order by d
>> d
>> ----------
>> 1
>> 10
>> 9
>>
>> select d from d order by cast(d as numeric)
>> d
>> ----------
>> 1
>> 9
>> 10
>>
>> select d from d order by
>> case when 1 = 1 then 1 else cast(d as numeric) end
>> d
>> ----------
>> 1
>> 10
>> 9
>>
>> select d from d order by
>> case when 1 = 1 then d else cast(d as numeric) end
>> d
>> ----------
>> 1
>> 9
>> 10
>
>Yes. The net result should be the same as in previous statement because
>1=1 is always true so your output should be ordered by the column "d".
>It does work when you use the number of the column in the select list as
>in the previous example. Good catch and thanks for sharing it with us.
>

Not, it is NOT wrong, it is RIGHT. The statement:

case when 1 = 1 then d else cast(d as numeric) end

returns either:

o d -- a string
o cast( d as numeric ) -- a number

the return value from that case would be a number.

Not totally relevant in this newsgroup however, as we would fail the statement to begin with.

ops$tkyte_at_ORA9I.WORLD> select * from (select '1' d from dual )   2 order by case when 1=1 then d else cast(d as numeric) end; order by case when 1=1 then d else cast(d as numeric) end

                                   *

ERROR at line 2:
ORA-00932: inconsistent datatypes

ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> select * from (select '1' d from dual )   2 order by case when 1=1 then to_number(d) else cast(d as numeric) end;

D
-
1

ops$tkyte_at_ORA9I.WORLD>

avoid implicit conversions!!

>
>
>--
>Mladen Gogala

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Dec 08 2001 - 13:47:01 CST