Conversion failed when converting the varchar value

Question

Post reply

Conversion failed when converting the varchar value

srathna77

SSCommitted

Points: 1982
More actions
December 18, 2008 at 10:04 am

#125111

Hi, Any HELP On this please
SELECT
CASE
WHEN NoMail NOT IN
(select emailaddress from Reports.dbo.OptOutEmailAddresses)
AND
substring
(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)
not in (select emaildomain from Reports.dbo.optoutemaildomains)
THEN '0'
WHEN NoMail = '' THEN '0'
ELSE NoMail
END as
NoMail
From customer
ERROR Msg:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '(serve 76330,456' to data type bit.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Is that the entire query or just a snippet?

Also, could you post the DDL for the tables involved?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What is the datatype of the column NoMail in the customer table? If it's bit, then that's the source of your error message.

Do any of the values of EMail not contain '@'?

Your query would be much easier to read if you used proper join syntax:

SELECT Email, NoMail

FROM customer c

LEFT JOIN Reports.dbo.OptOutEmailAddresses a ON a.emailaddress = c.Email

LEFT JOIN Reports.dbo.optoutemaildomains d

ON d.emaildomain = substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

Note that this is untested and the SUBSTRING will fail if the column Email contains values without '@'.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

srathna77 SSCommitted Points: 1982 More actions · Answer 3

Datatype for no email is bit in customer table i wnat to include this in a case statement.

SELECT

CASE

WHEN NoEMail NOT IN

(select emailaddress from Reports.dbo.OptOutEmailAddresses)

AND

substring

(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

not in (select emaildomain from Reports.dbo.optoutemaildomains)

THEN '0'

WHEN NoEMail = '' THEN '0'

ELSE NoEMail

END as

NoMail

From customer

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

srathna77 (12/18/2008)
Datatype for no email is bit in customer table i wnat to include this in a case statement.
SELECT
CASE
WHEN NoEMail NOT IN
(select emailaddress from Reports.dbo.OptOutEmailAddresses)
AND
substring
(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)
not in (select emaildomain from Reports.dbo.optoutemaildomains)
THEN '0'
WHEN NoEMail = '' THEN '0'
ELSE NoEMail
END as
NoMail
From customer

Sounds like you are tryin to compare a bit field to a string value and SQL can't convert a string to a bit. Which is exactly what the error message says in your original post.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

WHEN NoEMail = '' THEN '0'

I'm getting confused now between NoMail, which is bit, and Email, which is some kinda string. But whichever, you're trying to match one to the other.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 6

What are you trying to achieve with your query? Best explained to us in a straightforward way is to post table structure, sample data and required output. With all that, I'm sure we can help you. How to get all this is explained in the link below (the help us, help yourself line).

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

srathna77 SSCommitted Points: 1982 More actions · Answer 7

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else '0' end as NoMail From customer

Thanks for reading my post.

Srathna

srathna77 SSCommitted Points: 1982 More actions · Answer 8

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else '0' end as NoMail From customer

Thanks for reading my post.

Srathna

srathna77 SSCommitted Points: 1982 More actions · Answer 9

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else '0' end as NoMail From customer

Thanks for reading my post.

Srathna

srathna77 SSCommitted Points: 1982 More actions · Answer 10

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else '0' end as NoMail From customer

Thanks for reading my post.

Srathna

srathna77 SSCommitted Points: 1982 More actions · Answer 11

Got It Sir, My Bad

Corrected Query:

SELECT

CASE

When NOEmail = 1 then '1'

WHEN EMail

In (Select emailaddress from Reports.dbo.OptOutEmailAddresses) then '1'

When

Substring(Customer.Email, (CHARINDEX('@', Customer.Email)+1), 1000)

in

(select emaildomain from Reports.dbo.optoutemaildomains) then '1'

else '0' end as NoMail From customer

Thanks for reading my post.

Srathna

Ronald H SSCarpal Tunnel Points: 4053 More actions · Answer 12

After reading that 5 times, it sure comes through a lot better.. :crazy: Are you happy now with your query though? If not, please post the table structure, sample data and desired output.

Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

r.hensbergen (12/18/2008)
After reading that 5 times, it sure comes through a lot better.. :crazy: Are you happy now with your query though? If not, please post the table structure, sample data and desired output.

It was your crafty sig, Ron 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden