Logic error updating a column 3

[phil22046]

I wanted to update an entire column (January) in my report table. I need a different way to do this. I got this error when running the following:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

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

Select DISTINCT NAME, started, cnumber, CAST(started AS varchar(20)) AS cmonth,
MONTH(CAST(started AS varchar(20))) as nmonth, 00000000.00 AS JanAmt,
00000000.00 AS FebAmt, 00000000.00 AS MarAmt, 00000000.00 AS AprAmt,
00000000.00 AS MayAmt, 00000000.00 AS JunAmt, 00000000.00 AS JulAmt,
00000000.00 AS AugAmt, 00000000.00 AS SepAmt, 00000000.00 AS OctAmt,
00000000.00 AS NovAmt, 00000000.00 AS DecAmt
INTO dbo.Temp_ReportData
from confern
WHERE started >= CAST( @startingdate AS datetime)
order by 2;

Update .dbo.Temp_ReportData
SET JanAmt = JanAmt +
(Select cost FROM dbo.Temp_Detailstable
WHERE dbo.Temp_Detailstable.Cnumber = Temp_reportData.cnumber AND MONTH(CAST(dbo.Temp_Detailstable.dateentered AS varchar(20)))=1 AND
(dbo.Temp_Detailstable.Type='Registration' OR (dbo.Temp_Detailstable.Type = 'Discount' AND dbo.Temp_Detailstable.Item like 'UBS') OR dbo.Temp_Detailstable.Type='Credit') );
GO

 

[PDreyer]

... Select sum(cost) FROM ...

 

[phil22046]

I did that and got this error:

Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.

 

[PDreyer]

How about
Select sum(cost*1e0)

 

[SQLDenis]

your column datatype is not big enough for the sum of the values

example

declare @1 numeric(2,0),@2 numeric(2,0),@3 numeric(2,0)


select @1 =60,@2=60

select @1 +@2
select @3=@1 +@2

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

 

[SQLSister]

You need to learn how to do a lot of things. First you should not create temp tables this way. Use real temp tables that use tempdb (they start with #temp)or table variables instead. Table variables are ususally faster. Also create tables with a create table statement to control exactly what datatype you are using. It sounds as if you might not have a datatype large enough for the amount of data in the sum. Second, subqueries are to be avoided. Learn to use joins instead. Third, it sounds to me as if you are not completely clear on what your requirement is or how to effectively use SQL to meet your requirements. If you really understood what you are doing you would know that the subquery would have to return more than one value and thus would not work to update another value.

I'm not trying to be mean, but frankly from what I've seen so far, you would be better served to spend a week actually learning SQL without doing any actual changes to your production system. It appears to me that evertything you've written is making your database worse and eventually all those things will add up. Stop now and dig in and learn this stuff in depth before you cause more harm to your company.


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

 

[ESquared]

Ditto

 

[phil22046]

First let me explain that the final product will have #temp files. The reason I created database temp files is so I could have something to work with as an aid to development.

Anyway this is not being developed on the 'Live' database. It is only a copy. So I am not ruining anything.

I believe that my process for development is the correct approach. The database I have to work with has already many problems. The database integrity is already known not to be good. The various projects I am working are making an improvement.

I have a very flawed and bugging application program with no access to the source code. So I am making the best of a bad situation. Your comments might be more appropriate if the Data was pristine.

My prior experience has been mostly in the SQL Server Admin role. While I don't have a grasp of the exact SQL syntax, I do have 10 Years experience with SQL server, and another 30 years as a programmer / Analyst.

 

[SQLDenis]

I gave you the answer already

the numeric datatype is not BIG enough to hold the total value when you sum



instead of 00000000.00 AS DecAmt
do

convert(numeric(36,2),00000000.00) AS DecAmt

do that for the other months as well

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

 

[phil22046]

Haw can I get the result set to not have NULLs?

my latest SQL:

Update .dbo.Temp_ReportData
SET JanAmt = JanAmt +
(Select SUM(ISNULL(cost,0)) FROM dbo.Temp_Detailstable
WHERE dbo.Temp_Detailstable.Cnumber = Temp_reportData.cnumber AND MONTH(CAST(dbo.Temp_Detailstable.dateentered AS varchar(20)))=1 AND
(dbo.Temp_Detailstable.Type='Registration' OR (dbo.Temp_Detailstable.Type = 'Discount' AND dbo.Temp_Detailstable.Item like 'UBS') OR dbo.Temp_Detailstable.Type='Credit') ),
FebAmt = FebAmt +
(Select SUM(ISNULL(cost,0)) FROM dbo.Temp_Detailstable
WHERE dbo.Temp_Detailstable.Cnumber = Temp_reportData.cnumber AND MONTH(CAST(dbo.Temp_Detailstable.dateentered AS varchar(20)))=2 AND
(dbo.Temp_Detailstable.Type='Registration' OR (dbo.Temp_Detailstable.Type = 'Discount' AND dbo.Temp_Detailstable.Item like 'UBS') OR dbo.Temp_Detailstable.Type='Credit') ),
MarAmt = MarAmt +
(Select SUM(ISNULL(cost,0)) FROM dbo.Temp_Detailstable
WHERE dbo.Temp_Detailstable.Cnumber = Temp_reportData.cnumber AND MONTH(CAST(dbo.Temp_Detailstable.dateentered AS varchar(20)))=3 AND
(dbo.Temp_Detailstable.Type='Registration' OR (dbo.Temp_Detailstable.Type = 'Discount' AND dbo.Temp_Detailstable.Item like 'UBS') OR dbo.Temp_Detailstable.Type='Credit') );

 

[ESquared]

That sql is going to give me nightmares.

 

[phil22046]

Well its appropriate then, it is almost Halloween!

 

[ESquared]

It's not Halloween yet. Let's make the nightmares go away:

SELECT
   C.[Name],
   C.Started,
   C.CNumber,
   Jan = Sum(CASE WHEN Month(T.DateEntered) = 1 THEN Cost ELSE 0 END),
   Feb = Sum(CASE WHEN Month(T.DateEntered) = 2 THEN Cost ELSE 0 END),
   Mar = Sum(CASE WHEN Month(T.DateEntered) = 3 THEN Cost ELSE 0 END),
   Apr = Sum(CASE WHEN Month(T.DateEntered) = 4 THEN Cost ELSE 0 END),
   May = Sum(CASE WHEN Month(T.DateEntered) = 5 THEN Cost ELSE 0 END),
   Jun = Sum(CASE WHEN Month(T.DateEntered) = 6 THEN Cost ELSE 0 END),
   Jul = Sum(CASE WHEN Month(T.DateEntered) = 7 THEN Cost ELSE 0 END),
   Aug = Sum(CASE WHEN Month(T.DateEntered) = 8 THEN Cost ELSE 0 END),
   Sep = Sum(CASE WHEN Month(T.DateEntered) = 9 THEN Cost ELSE 0 END),
   Oct = Sum(CASE WHEN Month(T.DateEntered) = 10 THEN Cost ELSE 0 END),
   Nov = Sum(CASE WHEN Month(T.DateEntered) = 11 THEN Cost ELSE 0 END),
   [Dec] = Sum(CASE WHEN Month(T.DateEntered) = 12 THEN Cost ELSE 0 END)
FROM
   dbo.Confern C
   LEFT JOIN dbo.Temp_Detailstable T ON
      C.CNumber = T.CNumber
      AND (
         T.Type = 'Registration'
         OR T.Type = 'Credit'
         OR (
            T.Type = 'Discount'
            AND T.Item = 'UBS' -- Only use LIKE with wildcards
         )
      )
GROUP BY
   C.[Name],
   C.Started,
   C.CNumber

If you start adding more columns from Confer then you should make the LEFT JOIN clause a derived table with the sum in it and take the GROUP BY out of the main query.