Re: order by/case expression problem

From: Cyber Office <jcheong_at_cooper.com.hk>
Date: 9 Dec 2001 01:32:10 GMT
Message-ID: <9uueuq$k1t5@imsp212.netvigator.com>

Margolin, thanks for your reply. Now, I know the ANSI-SQL mechanism.

---

drop table e
create table e (e1 char(10), e2 numeric)

insert into e values ('2', 100)
insert into e values ('10',200)
insert into e values ('a',300)

select e1, e2 from e order by e1
 e1 e2

• --- 10 200 2 100 a 300

select e1, e2 from e order by e2
 e1 e2

• --- 2 100 10 200 a 300

select e1, e2 from e order by case when 1=2 then e1 else e2 end  e1 e2

• --- 2 100 10 200 a 300

select e1, e2 from e order by case when 1=1 then e1 else e2 end  Error converting data type varchar to numeric.

Sat, 8 Dec 2001 09:53:13 -0500 BP Margolin <bpmargo_at_attglobal.net> wrote:
> 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

>
>

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

Received on Sat Dec 08 2001 - 19:32:10 CST