Re: order by/case expression problem

From: Cyber Office <jcheong_at_cooper.com.hk>
Date: 9 Dec 2001 00:32:09 GMT
Message-ID: <9uube9$kpv3@imsp212.netvigator.com>

Thomas, thank you very much. I got the answer.

In Oracle or Sybase, implicit conversion within the CASE WHEN expression is not allowed.

However, in MSSQL, there is always implicit conversion from string to number. So, it just ignores the CASE WHEN condition, always jumps to numeric part, i.e. convert(numeric, col) or cast(col as numeric) Therefore, my Select statement is always "order by numeric" under such CASE expression.

In comp.databases.sybase Thomas Kyte <tkyte_at_us.oracle.com> wrote:
> 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
>

-- 
http://www-902.ibm.com/hk/sme/corner/solution_detail.html#a3
http://www.attunity.com

Received on Sat Dec 08 2001 - 18:32:09 CST