Incompatible data types - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Incompatible data types

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 10:53:46

Another question on the same database.
I'm trying to execute this script:
SELECT SUBJECTS.NAME, RECEIPTS.DEFENDANT
FROM CIVIL.dbo.SUBJECTS SUBJECTS
INNER JOIN
CIVIL.dbo.RECEIPTS RECEIPTS
ON (SUBJECTS.NAME = RECEIPTS.DEFENDANT)
I'm trying to find entries in the Subjects table where the "Name" column equals the value of the "Defendant" column in the Receipts table. Problem is, the "Name" column in Subjects table is a Varchar(70) data type and the "Defendant" column in the Receipts table is a Text data type. The results of the inner join says that the two data types are incompatible. Is there a way to "convert" the way the data type in the defendant column for the purposes of this join? If so, what would my script look like?
p.s., i know that the Text datatype is deprecated in SQL 2008, but someone needs to inform our Engineers of that. They don't listen to the DBA's.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:56:03

[code]
SELECT s.NAME, r.DEFENDANT
FROM CIVIL.dbo.SUBJECTS s
INNER JOIN CIVIL.dbo.RECEIPTS r
ON s.NAME = CAST(r.DEFENDANT AS varchar(max))
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-07 : 10:57:47

Awesome. Thank you! Somehow i thought it would be more complicated than that.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-07 : 11:12:52

If Name in the subjects table is Varchar(70), I'd suggest cast to Varchar(70), not to varchar(max)

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 11:18:55

quote:
Originally posted by WJHamel

Awesome. Thank you! Somehow i thought it would be more complicated than that.

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources