Re: Converting a field from int to varchar during query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 02/03/05 

Date: Thu, 03 Feb 2005 22:49:49 +0100

On Thu, 3 Feb 2005 13:01:08 -0800, Jig Bhakta wrote:

>Nope, still getting the error.

Hi Jignesh,

In that case, you better post the complete query. Best is to include
CREATE TABLE statements and INSERT statements with some sample data to
reproduce the error.

Here's a small script that shows that my change does work on my system, so
your error is either caused by something else in your query, by something
strange in your data or by a SQL Server bug. If you post a script that
will reproduce the error, we can find which of these three is the cause.

-- Start of repro script that show the error is corrected

create table table1 (field1 int not null)
create table table2 (field2 varchar(20) not null)
go
insert table1 (field1) select 1 union all select 2
insert table2 (field2) select '1' union all select 'XXXX'
go
print 'Original'
print ''
select * from table1, table2
where table1.field1 = table2.field2
print '-----'
go
print ''
print 'Corrected'
print ''
select * from table1, table2
where CAST(table1.field1 AS varchar(10)) = table2.field2
print '-----'
go
drop table table1
drop table table2
go

-- Output:

Original
 
field1 field2
----------- --------------------
1 1

Server: Msg 245, Level 16, State 1, Line 3
Syntax error converting the varchar value 'XXXX' to a column of data type
int.
 
Corrected
 
field1 field2
----------- --------------------
1 1

-----

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages