Re: order by/case expression problem

In article <9utis6$bp83_at_imsp212.netvigator.com>, Cyber says...
>
>Sat, 08 Dec 2001 11:20:58 +0100 Svend Jensen <Master_at_oraclecare.com> wrote:
>> Cyber Office wrote:
>>>
>>> The last select statement in the following batch seems to return incorrect
>>> result.
>>>
>>> Am I right?

no,

the case 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

In this case, the rules say -- the return value from the case statement (which must be CONSISTENT, it cannot return a number for one row and a string for the next) is a NUMBER, a string will be implicitly converted to a number in this case.

Your case statement is exactly the same as:

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

so, it sorts as a number would.

The type of operands in the the boolean expression are not relevant, they can be anything as long as they evaluate to TRUE, FALSE or UNKNOWN (null).

Only the return values in the CASE statement count and in this case, this case statement returns a NUMBER.

>>>
>>> --------------------------------------------------------------------------
>>> 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
>>>
>>> --
>>> http://www-902.ibm.com/hk/sme/corner/solution_detail.html#a3
>>> http://www.attunity.com
>>
>> Hi
>>
>> Your select has a implicit to_number conversion (1 = 1),
>> might try with 1 = '1'
>>
>I have tried the following tests. But still get the same result
>
>select d from d order by
>case when 1 = '1' then d else cast(d as numeric) end
> d
> ----------
> 1
> 9
> 10
>
>select d from d order by
>case when 1 <> '1' then d else cast(d as numeric) end
> d
> ----------
> 1
> 9
> 10
>
>
>
>--
>http://www-902.ibm.com/hk/sme/corner/solution_detail.html#a3
>http://www.attunity.com

--
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