Re: order by/case expression problem

From: BP Margolin <bpmargo_at_attglobal.net>
Date: Sat, 8 Dec 2001 09:53:13 -0500
Message-ID: <3c122862_2@news3.prserv.net>

CASE is an expression. That means that it returns a value of a specific data type. SQL Server, and any ANSI SQL compliant RDBMS, will determine a single data type for the CASE expression, and then will implicitly convert all return values to that data type.

As Svend posted, because the last query is formulated as:

order by

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

SQL Server must "choose" between the data type of column "d" (char) and numeric. The rules of ANSI SQL require that the char be implicitly converted to numeric.

So, in short, SQL Server is operating correcting.

---

BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which can be cut and pasted into Query Analyzer is appreciated.

"Cyber Office" <jcheong_at_cooper.com.hk> wrote in message news:9us7lj$2qb1_at_imsp212.netvigator.com...
> 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
Received on Sat Dec 08 2001 - 08:53:13 CST