Error Message

[phil22046]

the SQL I am running is this:
DECLARE @startingdate datetime;
DECLARE @endingdate datetime;
SET @startingdate = '01/01/2007';
SET @endingdate = '12/31/2007';

IF OBJECT_ID (N'dbo.Temp_ReportData', N'U') IS NOT NULL
DROP TABLE dbo.Temp_ReportData;

SELECT
ITEM AS NAME,
INVDATE AS STARTED,
CNumber,
JanAmt = Sum(CASE WHEN Month(INVDATE) = 1 THEN Cost ELSE 0 END),
FebAmt = Sum(CASE WHEN Month(INVDATE) = 2 THEN Cost ELSE 0 END),
MarAmt = Sum(CASE WHEN Month(INVDATE) = 3 THEN Cost ELSE 0 END),
AprAmt = Sum(CASE WHEN Month(INVDATE) = 4 THEN Cost ELSE 0 END),
MayAmt = Sum(CASE WHEN Month(INVDATE) = 5 THEN Cost ELSE 0 END),
JunAmt = Sum(CASE WHEN Month(INVDATE) = 6 THEN Cost ELSE 0 END),
JulAmt = Sum(CASE WHEN Month(INVDATE) = 7 THEN Cost ELSE 0 END),
Augamt = Sum(CASE WHEN Month(INVDATE) = 8 THEN Cost ELSE 0 END),
Sepamt = Sum(CASE WHEN Month(INVDATE) = 9 THEN Cost ELSE 0 END),
Octamt = Sum(CASE WHEN Month(INVDATE) = 10 THEN Cost ELSE 0 END),
Novamt = Sum(CASE WHEN Month(INVDATE) = 11 THEN Cost ELSE 0 END),
DecAmt = Sum(CASE WHEN Month(INVDATE) = 12 THEN Cost ELSE 0 END)
INTO dbo.Temp_ReportData
FROM
InvItem
WHERE invdate >= @startingdate AND invdate <= @endingdate AND VOID = 0 AND
( Type IN ('Booth','Ad','Sponsorship'))
GROUP BY
INVDATE,
ITEM,
CNumber;

GO

The error message it produces: Server: Msg 245, Level 16, State 1, Line 9
Syntax error converting the varchar value '350.00' to a column of data type int.

I have been looking for this record and can't locate it. How can I determine which record has the bad data?

 

[SQLDenis]

select * from InvItem
where cost ='350.00'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLDenis]

why are you storing that in an int datatype anyway don't need decimal instead?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[phil22046]

OK I get 256 records when I run that, maybe there ar4e 256 bad records. I was thinking that there was only one bad record and was trying to narrow it down.

 

[phil22046]

How can I get back something other than an Int?

 

[SQLDenis]

does this work?

SELECT
   ITEM AS NAME,
   INVDATE AS STARTED,
   CNumber,
   JanAmt = Sum(CASE WHEN Month(INVDATE) = 1 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   FebAmt = Sum(CASE WHEN Month(INVDATE) = 2 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   MarAmt = Sum(CASE WHEN Month(INVDATE) = 3 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   AprAmt = Sum(CASE WHEN Month(INVDATE) = 4 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   MayAmt = Sum(CASE WHEN Month(INVDATE) = 5 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   JunAmt = Sum(CASE WHEN Month(INVDATE) = 6 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   JulAmt = Sum(CASE WHEN Month(INVDATE) = 7 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   Augamt = Sum(CASE WHEN Month(INVDATE) = 8 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   Sepamt = Sum(CASE WHEN Month(INVDATE) = 9 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   Octamt = Sum(CASE WHEN Month(INVDATE) = 10 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   Novamt = Sum(CASE WHEN Month(INVDATE) = 11 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   DecAmt = Sum(CASE WHEN Month(INVDATE) = 12 THEN convert(decimal(20,5),Cost) ELSE '0' END)
INTO dbo.Temp_ReportData
FROM
   InvItem 

GROUP BY
   INVDATE,
   ITEM,
   CNumber;

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[phil22046]

Not Quite

I got: Server: Msg 8114, Level 16, State 5, Line 4
Error converting data type varchar to numeric.

 

[SQLDenis]

then you have bad data, classic case of GIGO

This is why you store the data in an appropriate data type and you don't have to horse around down the road!!!!!!

Why is it stored in a varchar to begin with if it is cost?


run this

select * from invItem where isnumeric(cost) =0


here is a test for the previous post

select JanAmt = Sum(CASE WHEN (INVDATE) = 1 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   FebAmt = Sum(CASE WHEN (INVDATE) = 2 THEN convert(decimal(20,5),Cost) ELSE '0' END),
   MarAmt = Sum(CASE WHEN (INVDATE) = 3 THEN convert(decimal(20,5),Cost) ELSE '0' END)
 from(
select '350' Cost,1 as INVDATE
union all
select '350' Cost,2 as INVDATE
union all
select '350' Cost,1 as INVDATE
union all
select '350' Cost,3 as INVDATE) x

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[phil22046]

I agree with you, GIGO. But I am inheriting this system.

The Vendor who wrote this system for us obviously didn't know how to design a database.

I have to deal with tables with no primary key, never normalized data and no help from the vendor.

 

[SQLDenis]

anyway what I gave you should help you out ;-0

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[SQLSister]

Maybe you should create the table first rather than using select into?

Then you could define the records with the same datatype that the existing table uses. Select into is using its best guess at the data depending on either conversion in the query or the data at the begining of the result set. That said, you probably still need to find and clean up the bad data before you can move on to a better structure.

Another thing to try is to specifically exclude known bad data points from the query with isnumeric(cost) =1 in the where clause.

"NOTHING is more important in a database than integrity." ESquared