[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Comparing SmallInt and VarChar?

Posted on 2006-03-26
4
Medium Priority
?
1,301 Views
Last Modified: 2008-03-06
Hi, I want to run the following query:

SELECT ShortName, InsurerID
FROM Insurer
WHERE InsurerID IN (
            SELECT Description
            FROM Accounts
            WHERE AttorneyID = @att)
ORDER BY ShortName

The problem is that InsurerID is datatype SmallInt and Description is datatype VarChar.  These are neccessary differences, so changing the database is out of the question.

How can I cast one into the other to make this query work?

Thanks.
0
Comment
Question by:brassmonkeyboy
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 16297986
try


SELECT ShortName, InsurerID
FROM Insurer
WHERE convert(varchar, InsurerID ) IN (
            SELECT Description
            FROM Accounts
            WHERE AttorneyID = @att)
ORDER BY ShortName
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16297998
if possible, use this:

SELECT ShortName, InsurerID
FROM Insurer
WHERE InsurerID IN (
            SELECT cast(Description as int)
            FROM Accounts
            WHERE AttorneyID = @att)
ORDER BY ShortName

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 16298019
SELECT ShortName, InsurerID
from insurer
inner join
accounts
on cast(InsurerID as varchar(100)) = description
where attorneyid =@att order by shortName
0
 

Author Comment

by:brassmonkeyboy
ID: 16298024
Appari, that worked perfectly.  Thank you.

Angel, that didn't work...I'd actually already tried it.  Thank you though!

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question