Conversion failed converting varchar to int

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

SQL Server 2005 Forums

Transact-SQL (2005)

Conversion failed converting varchar to int

Author

Topic

jbuttery
Starting Member

4 Posts

Posted - 2008-02-01 : 15:03:08

I can't seem to get my arms around this. The 1st query works, but whwn I try to use results it fails. I thought varchar to int was implicit.

Use ItemsDB

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2
Group by DataID
order by '# Items' desc

DataID # Items
126351 2192
57618 1640
53968 1202
60068 556
14618 538
57832 502
31446 382
57795 361
58728 304

This fails with the error.

Use ItemsDB

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2 and '# Items' >= 20

Group by DataID
order by '# Items' desc

Conversion failed when converting the varchar value '# Items' to data type int.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-02-01 : 15:21:48

Try this:

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2 and Count(DataID) >= 20
Group by DataID
order by '# Items' desc

Your example is getting the error because it's trying to compare the string '# Items' to the number 20 and not the actual number of items to the number 20. In other words it's trying to compare text to an int.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-01 : 23:29:10

You cant use aliases in WHERE conditions. You need to use actual values there. Alises can be used only in ORDER BY clause.

jbuttery
Starting Member

4 Posts

Posted - 2008-02-04 : 12:51:24

You also can't use aggregates in a WHERE clause

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

The following works without syntax errors:

Use ItemsDB

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2
Group by DataID
having Cast(Count(DataID) AS int) >= 20
order by '# Items' desc

But I need to use the int result from the cast statement for an update operation. I think I need to use the Select in a sub-query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 12:55:29

Exactly,it will be like
UPDATE t1
SET t1.Field=t2.value
FROM Table1 t1
INNER JOIN (your query batch here)t2
ON t2.matchingfield=t1.matchingfield
remember to use aliases for all columns used in subquery.

thedryden
Starting Member

23 Posts

Posted - 2008-02-04 : 12:56:39

The following would also work:

Select DataID
   , # Items
From (
SELECT DataID
   , Count(DataID) '# Items' 
FROM ItemsTable
WHERE ItemFlag = 2
) a
WHERE # Items >= 20

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-05 : 01:31:42

A COUNT() can only return an integer. Not even NULL.

SELECT		DataID,
		COUNT(DataID) AS [# Items]
FROM		ItemsTable
WHERE		ItemFlag = 2 
GROUP BY	DataID
HAVING		COUNT(DataID) >= 20
ORDER BY	COUNT(DataID) DESC

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-05 : 08:18:47

quote:
Originally posted by jbuttery

I can't seem to get my arms around this. The 1st query works, but whwn I try to use results it fails. I thought varchar to int was implicit.

Use ItemsDB

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2
Group by DataID
order by '# Items' desc

DataID # Items
126351 2192
57618 1640
53968 1202
60068 556
14618 538
57832 502
31446 382
57795 361
58728 304

This fails with the error.

Use ItemsDB

Select DataID, Count(DataID) '# Items' from ItemsTable
where ItemFlag = 2 and '# Items' >= 20

Group by DataID
order by '# Items' desc

Conversion failed when converting the varchar value '# Items' to data type int.

Avoid having single quotes around the alias names. Use proper alias names like no_of_items

Madhivanan

Failing to plan is Planning to fail

jbuttery
Starting Member

4 Posts

Posted - 2008-02-05 : 12:01:03

Thanks for the help everyone. I'll play with those and post back.

jbuttery
Starting Member

4 Posts

Posted - 2008-02-06 : 12:53:46

I thought I had tried an IN sub-query as I'm using two different DB providers, but this is what finally did it.

USE ItemsDB
Update ItemsTable set ItemFlag = 0
where DataID in
(Select DataID from ItemsTable
Group by DataID
having Cast(Count(DataID) AS int) <= 4
and ItemFlag = 2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-06 : 13:18:02

can use join also:-
USE ItemsDB
Update it
set it.ItemFlag = 0
FROM ItemsTable it
INNER JOIN
(Select DataID from ItemsTable
Group by DataID
having Cast(Count(DataID) AS int) <= 4
and ItemFlag = 2)t
ON t.DataID =it.DataID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources