What does this error mean - 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

What does this error mean

Author

Topic

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 09:33:02

I have a query that is receiving the following error:

syntax error converting the varchar '66,75' to a column of data type int.

I can't figure out what the issue is. Here is my query:

SELECT COUNT(*) AS Expr1
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = 1) AND (sub_email IS NULL) AND (sub_user_number IS NOT NULL) AND (active = 1) AND (sub_group_id LIKE '% 7 %')

Do any of you sql experts see where my error is at?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-09 : 09:57:00

If account_id or group id are strings, you need to wrap quotes around the values you're comparing them to.

--
Gail Shaw
SQL Server MVP

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 10:02:25

I dont think it is those 2 because I was running the query successfully. I probably should have mentioned this before, but I basically only switched the values of sub_email and sub_user_number. After that it broke. I changed sub_email to 'IS NULL' and changed sub_user_number to 'IS NOT NULL'. Now it throws that error.

Just to confirm, I checked the query again and this works:

SELECT COUNT(*) AS Expr1
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = 1) AND (sub_group_id LIKE '%1%') AND (sub_email IS NOT NULL) AND (sub_user_number IS NULL) AND (active = 1)

And this breaks

SELECT COUNT(*) AS Expr1
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = 1) AND (sub_email IS NULL) AND (sub_user_number IS NOT NULL) AND (active = 1) AND (sub_group_id LIKE '%1%')

The only change was to the email and sub_user_number conditions. Does not make sense.

Upon further investigation, I see the sub_user_number field is a char. I am not sure , but does that make a difference? Why would IS NULL work for it but not IS NOT NULL. I am very confused.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-09 : 10:39:19

Are the Account_id and group_id fields string or numbers?

The error likely has to do with the data returned, so some of the where clause parameters allow data through that fails. Did you try my advice or did you just discount it without investigation?
I suspect those fields as they are the only place where there's a possible data conversion happening.

--
Gail Shaw
SQL Server MVP

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 10:41:02

I tried your advice, and the query ran, but it returned incorrect data of 0. Those fields are integers in the DB. When I ran the query before I changed it, it ran correctly and gave me all the correct results I needed. I don't understand this.

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 10:43:54

Can you provide the table layout for the Subscription table and some sample data? Did you look at the active field, that's another place where an implicit conversion could be happening

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 10:57:46

account_id int 4 1
sub_user_number char 10 1
sub_email varchar 255 1
group_id varchar 4000 1
sub_group_id varchar 4000 1
active char 1 1

account_id 119
sub_user_number 7862496306
sub_email jdoe@much.com
group_id 2
sub_group_id 5,7,8
active 1

I don't think the DB is set up correctly, but I inherited this situation.

I have changed the query to this:

SELECT COUNT(*) AS Expr1
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = '1') AND (sub_group_id LIKE '%1%') AND (sub_email IS NULL) AND (sub_user_number IS NOT NULL) AND (active = '1')

I am not getting an error, yet I am getting a count of 0 which I know is incorrect.

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 11:04:36

Thanks for all the help, I hope nobody gets frustrated with me. I inherited a position that wears many hats, some I have little experience in. SQL Server is one of them. I feel like I have been living in this forum lately.

I just don't understand why when i change the condition of the sub_user_number, I start getting 0 results. Although all of your help to this point has eliminated the errors.

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-09-09 : 11:22:54

It looks like the issue is with the group_id column. When you change the results from IS NULL to NOT IS NULL, the database is bringing back different records based on the criteria. Since you were asking group_id = 1, it is implicitly trying to convert the data in the group_id field to an integer. That's why you are getting the error "converting the varchar '66,75' to a column of data type int." because 66,75 cannot be converted to an integer. You will have to say where group_id = '1' or fix the bad data if indeed it is bad

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

jatrix32
Starting Member

49 Posts

Posted - 2010-09-09 : 11:40:50

Thank you for all your help MSquared and Gila Monster. The query is now functioning correctly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources