Conversion failed when converting the varchar valu

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

Conversion failed when converting the varchar valu

Author

Topic

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-07-28 : 14:36:35

I'm running this code and I'm getting the following error

Conversion failed when converting the varchar value 'Sum of apr' to data type int.

SELECT [Shipment].[load],
[Shipment].[ship],
SUM([Shipment].[estimate]) AS [est],
SUM([Shipment].[approved]) AS [apr],
[Shipment].[est acs] AS [Sum of est],
[Shipment].[apr acs] AS [Sum of apr],
CASE WHEN 'Sum of apr' = 0
OR 'Sum of apr' IS NULL
THEN 'Sum of est'
ELSE 'Sum of apr' END AS [other acs]
INTO [other_acs47]
FROM [Shipment]
GROUP BY [load],[ship],[estimate],[approved];

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-28 : 14:42:29

It's because of this:
CASE WHEN 'Sum of apr' = 0
OR 'Sum of apr' IS NULL
THEN 'Sum of est'
ELSE 'Sum of apr' END AS [other acs]

Perhaps you want this instead:
CASE WHEN 'Sum of apr' = '0'
OR 'Sum of apr' IS NULL
THEN 'Sum of est'
ELSE 'Sum of apr' END AS [other acs]

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-28 : 14:49:59

I'm not sure that's it...could be

BUT


[Shipment].[apr acs] AS [Sum of apr],
CASE WHEN 'Sum of apr' = 0

You can't reference the label in the same select, I don't think (see, that always happens to me)

You need to post the DDL for us to help you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rob41
Yak Posting Veteran

67 Posts

Posted - 2010-07-28 : 14:52:16

Tara, thanks it worked like a charm you rock!!!!

X002548
Not Just a Number

15586 Posts

Posted - 2010-07-28 : 14:56:28

Well this is just flat out wrong..inconsistent grouping, ect


SELECT  [load]
	  , [ship]
	  , SUM([estimate]) AS [est]
	  , SUM([approved]) AS [apr]
	  , [est acs] AS [Sum of est]
	  , [apr acs] AS [Sum of apr]
...
...
GROUP BY [load],[ship],[estimate],[approved];

What is this???

, [est acs] AS [Sum of est]
, [apr acs] AS [Sum of apr]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources