Hi,
The following code describes a problem I'm having when I'm selecting
data via a Case statement.
The data I'm selecting is from a table with 2 varchar columns and 1
int column. When I select this data I get the following error:
Server: Msg245, Level 16,State 1, Line 1
Syntax error converting the varchar values 'A' to a column of datatype
int.
If I cast the int to a varchar it works, but I would prefer not to
cast if possible. SQL server seems to look at all the datatypes in the
case statement and if a int data type appears, it seems to be trying
to insert into a int column. Is this some sort of temp table? Anyone
know why it assigned an int column here?
Any ideas what I can do
Thanks,
Tom
- --
- -- Set up the table
- --
create table Tom
(charcol1 varchar(2)
,charcol2 varchar(2)
,intcol1 int
)
insert into tom
(charcol1
,intcol1
,charcol2
)
values
('A',1,'C')
- ---
- -- Code
- ---
SELECT
CASE numlist.list
WHEN 1 THEN
Tom.charcol1
WHEN 2 THEN
--cast (Tom.intcol1 as varchar(5))
Tom.intcol1
WHEN 3 THEN
Tom.charcol2
END AS result
FROM Tom
CROSS JOIN (select 1 as list
union all
select 2 as list
union all
select 3 as list
) as numlistThis is how CASE work, result from CASE expression is of the same datatype.
Output is determined as
per "datatype precedence" documented in Books Online.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Staggly" <tomstagg@.gmail.com> wrote in message
news:e646e15f.0502090622.8a26408@.posting.google.com...
> Hi,
> The following code describes a problem I'm having when I'm selecting
> data via a Case statement.
> The data I'm selecting is from a table with 2 varchar columns and 1
> int column. When I select this data I get the following error:
> Server: Msg245, Level 16,State 1, Line 1
> Syntax error converting the varchar values 'A' to a column of datatype
> int.
> If I cast the int to a varchar it works, but I would prefer not to
> cast if possible. SQL server seems to look at all the datatypes in the
> case statement and if a int data type appears, it seems to be trying
> to insert into a int column. Is this some sort of temp table? Anyone
> know why it assigned an int column here?
> Any ideas what I can do
> Thanks,
> Tom
> - --
> - -- Set up the table
> - --
> create table Tom
> (charcol1 varchar(2)
> ,charcol2 varchar(2)
> ,intcol1 int
> )
> insert into tom
> (charcol1
> ,intcol1
> ,charcol2
> )
> values
> ('A',1,'C')
> - ---
> - -- Code
> - ---
> SELECT
> CASE numlist.list
> WHEN 1 THEN
> Tom.charcol1
> WHEN 2 THEN
> --cast (Tom.intcol1 as varchar(5))
> Tom.intcol1
> WHEN 3 THEN
> Tom.charcol2
> END AS result
> FROM Tom
> CROSS JOIN (select 1 as list
> union all
> select 2 as list
> union all
> select 3 as list
> ) as numlist
No comments:
Post a Comment