Re: order by/case expression problem

From: Cyber Office <jcheong_at_cooper.com.hk>
Date: 8 Dec 2001 17:32:54 GMT
Message-ID: <9utis6$bp83@imsp212.netvigator.com>

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

Received on Sat Dec 08 2001 - 11:32:54 CST