Re: order by/case expression problem

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'

/svend Received on Sat Dec 08 2001 - 04:20:58 CST