We help IT Professionals succeed at work.

Comparing SmallInt and VarChar?

brassmonkeyboy
on
Medium Priority
1,329 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.
Comment
Watch Question

Commented:
try


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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Top Expert 2006

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

Author

Commented:
Appari, that worked perfectly.  Thank you.

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

Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.