Update a summed column - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update a summed column

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 13:13:32

I need to sum a column based on the dealercode and update a table with the value.

I know there's a cursor involved here, but try to pretend it doesn't exist. :)

My dilemma is that I can get the value of sum(fedxmtfee)to print on the screen, but I don't seem to be able to assign that value to a variable with which to update the table. I originally tried it without the select as... Total, but that didn't work either. Now it tells me that it's an invalid column name. (The cursor portion work because I use it to get row counts for other criteria I need. I only posted the code that will not work here to save space.)

Any help would be greatly appreciated.

Thanks!
Teresa

declare @fedxmt int, @dlrcode varchar(3)

declare prep_cursor cursor for
select distinct dlrcode
from dbo.Efile
where dbo.Efile.dlrcode is not null
open prep_cursor
fetch next from prep_cursor
into @dlrcode
while @@fetch_status = 0
begin
print @dlrcode
--get the total fedxmtfee
select sum(fedxmtfee) as total
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempEfiles ON
dbo.Efile.DLRCODE = dbo.tempEfiles.DLRCODE
where dbo.efile.dlrcode = @dlrcode and tempefiles.fedxmtfee is not null
set @fedxmt = total
update efile set YTDFEDXMT = @fedxmt
set @fedxmt = 0
fetch next from prep_cursor
into @dlrcode
end
close prep_cursor
deallocate prep_cursor
go

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 13:31:17

UPDATE A
SET A.YTDFEDXMT=B.Total
FROM dbo.Efile A INNER JOIN
(SELECT efile.dlrcode, sum(tempEfiles.fedxmtfee) Total
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempEfiles ON
dbo.Efile.DLRCODE = dbo.tempEfiles.DLRCODE
where tempefiles.fedxmtfee is not null) B
ON A.dlrcode=B.dlrcode

Not 100% sure this will work, but it should be close. You may need to test the inner SELECT query to see if it works.

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 15:40:09

Rob,

I used your code and have arranged and rearranged, assigned new names and reassigned new names to the tables and I still get this error message:

quote:

Invalid object name 'tempEfiles'.

What am I missing?

Thanks,
Teresa

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 15:43:35

I didn't fully qualify all of the names, or alias the tables, try this:

UPDATE A SET A.YTDFEDXMT=B.Total
FROM Efile A INNER JOIN
(SELECT E.dlrcode, sum(T.fedxmtfee) Total
FROM Efile E LEFT OUTER JOIN
tempEfiles T ON
E.DLRCODE = T.DLRCODE
WHERE T.fedxmtfee is not null) B
ON A.dlrcode=B.dlrcode

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 16:04:40

quote:
Invalid object name 'tempEfiles'.

Here's me with EGG on my face!

I was trying to execute on the wrong server!!! I'm so sorry!

This code works (it needed a group by clause)

UPDATE A SET A.YTDFEDXMT=B.Total
FROM Efile A INNER JOIN
(SELECT e.dlrcode, sum(T.fedxmtfee) Total
FROM Efile E LEFT OUTER JOIN
tempEfiles T ON
E.DLRCODE = T.DLRCODE
WHERE T.fedxmtfee is not null group by E.dlrcode) B
ON A.dlrcode=B.dlrcode

Will you help me with this? I can understand the A and the T, but not the B?

Thanks!
Teresa

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 16:12:21

Man, those GROUP BY clauses HATE me! I'm always forgetting them!

I can top that: ever copy a script or program file, spend 2 hours editing the copy, and wondering why when you run THE ORIGINAL it still fails? (actually, you might!) Let's just say it also happens on web pages

Anyway, whenever you have a subquery/derived table, you need to apply an alias to it:

SELECT * FROM
(SELECT * FROM Orders) A

SQL Server needs the alias in order to identify or reference the subquery as a table, in effect. You don't need to use the alias in the outer part of the query, but it still needs to be there.

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 16:28:21

quote:

I can top that: ever copy a script or program file, spend 2 hours editing the copy, and wondering why when you run THE ORIGINAL it still fails?

This definitely sounds like something I would do!

This is taking a very long time to run. Is there anyway, other than removing the cursor process, to speed it up? I have 7 other functions that will run inside this cursor as well and without this update it takes approx 10 sec to run. Running this code inside the cursor without the rest of the functions is taking 15+ minutes.

Thanks again!
Teresa

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 16:35:07

NO! This statement should NOT be running inside the cursor at all! It completely replaces the code you have in your original post!

If you didn't post ALL of your code originally, allow me to (gently but firmly) pound you over the head! What other functions does the procedure do?

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 16:54:15

quote:

What other functions does the procedure do?

I have to add 4 more processes. All four of them are summing functions.

I don't have to add it in here, but it needs to run before or after this proc. I thought it would be help save time (obviously not!) to add it in here!

Here is the rest of it...

declare @dlrcode varchar(3),@message varchar(80),@primssn varchar(9)
declare @rtn varchar (12), @DAN varchar (17), @trancode varchar
declare @DirState varchar, @PiggyState varchar, @acctnum varchar
declare @cntTaxRows smallint, @cntCurEfiles smallint, @cntEfile smallint
declare @cntTotEfile smallint

set @cntTotEfile = 0
set @cntCurEfiles = 0
set @cnttaxrows = 0
set @cntEfile = 0

declare prep_cursor cursor for
select distinct dlrcode
from dbo.Efile
where dbo.Efile.dlrcode is not null
open prep_cursor
fetch next from prep_cursor
into @dlrcode
while @@fetch_status = 0
begin
print @dlrcode
--get the funded qiks
set @cntTaxRows = 0
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempQIK ON
dbo.Efile.DLRCODE = dbo.tempQIK.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempQIK.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempQIK.primssn is not null and tempfloan.trancode = 'ACH'
set @cntTaxRows = @@rowcount
update Efile set actualqiks = @cnttaxrows where dlrcode = @dlrcode
set @cnttaxrows = 0
--get the unfunded qiks
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempQIK ON
dbo.Efile.DLRCODE = dbo.tempQIK.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempQIK.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempQIK.primssn is not null and tempFLoan.trancode is null
set @cntTaxRows = @@rowcount --eof
update Efile set NOFUNDQIK = @cnttaxrows where dlrcode = @dlrcode
set @cnttaxrows = 0
--get the funded rals
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempRAL ON
dbo.Efile.DLRCODE = dbo.tempRAL.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempRAL.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempRAL.primssn is not null and tempfloan.trancode = 'ACH'
set @cntTaxRows = @@rowcount --eof
update Efile set actualrals = @cnttaxrows where dlrcode = @dlrcode
set @cnttaxrows = 0
--get the unfunded rals
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempRAL ON
dbo.Efile.DLRCODE = dbo.tempRAL.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempRAL.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempRAL.primssn is not null and tempFLoan.trancode is null
set @cntTaxRows = @@rowcount --eof
update Efile set nofundral = @cnttaxrows where dlrcode = @dlrcode
set @cnttaxrows = 0
--get the efiles
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempEfiles ON
dbo.Efile.DLRCODE = dbo.tempEfiles.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempEfiles.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempEfiles.primssn is not null
set @cntTaxRows = @@rowcount --eof
update Efile set ACTUALEFIL = @cntTaxRows where dlrcode = @dlrcode
set @cnttaxrows = 0
--get dir state files
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempTaxDirState ON
dbo.Efile.DLRCODE = dbo.tempTaxDirState.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempTaxDirState.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempTaxDirState.primssn is not null
set @cntTaxRows = @@rowcount --eof
update Efile set ACTUALDIR = @cnttaxrows where dlrcode = @DLRCODE
set @cnttaxrows = 0
--get piggy back files
select distinct primssn, accptcode, dan, rtn, dirstate, piggystate
FROM dbo.Efile LEFT OUTER JOIN
dbo.tempTaxPiggy ON
dbo.Efile.DLRCODE = dbo.tempTaxPiggy.DLRCODE LEFT OUTER JOIN
dbo.tempFLoan ON
dbo.tempTaxPiggy.primssn = dbo.tempFLoan.acctnum
where dbo.Efile.dlrcode = @dlrcode and tempTaxPiggy.primssn is not null
set @cntTaxRows = @@rowcount --eof
update Efile set ACTUALPIG = @cnttaxrows where dlrcode = @DLRCODE
set @cnttaxrows = 0
fetch next from prep_cursor
into @dlrcode
end
close prep_cursor
deallocate prep_cursor
go

quote:
I now know why I wasn't given any sharp objects when I started working here!

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:10:34

Mind if I wait until you've got them all finished? Somehow I think I should

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-14 : 17:17:37

quote:

Mind if I wait until you've got them all finished? Somehow I think I should

That's why you're the 'GURU'

I actually think I've got a handle on it now. I'll run the proc that creates the temp tables, then the cursor proc, then a new proc to sum the columns, then delete the temp tables.

Please keep in mind that I created that cursor proc on my own, before I found this site, and before I found out that cursors are BAD!

If you still want me to post it all, let me know.

Thank you so much for your patience and your help!
Teresa

"Happiness is found along the way;
not at the end of the road."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-03-14 : 17:28:39

Yeah, whenever you get it done. No rush.

If the operation is the same for each step, you could use the same technique for each of the operations: compare the old code to the new and make the appropriate changes. If it seems obvious, try it. If not, post the whole thing here and I'll take a look.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2002-03-25 : 10:46:17

>> I need to sum a column based on the dealercode and update a table with the value. <<
No, you need to create a VIEW, which will be updated automatically and will always be correct. Otherwise, your databse is carrying redundant data.

>> I know there's a cursor involved here, but try to pretend it doesn't exist. :) <<

You should never write more than five cursor in your entire career; and three of them could have been done better in non-procedural code in the next release of the SQL engine.

Your problem is that the solutions which were posted here all used the UPDATE .. FROM syntax. This is proprietary and does not port even to other SQL products (Sybase, Ingres) that copied the syntax, but not the current semantics.

>> My dilemma is that I can get the value of sum(fedxmtfee)to print on the screen, but I don't seem to be able to assign that value to a variable with which to update the table. <<

You do not work with a single value at a time in SQL; you work with the entire set all at once. You are still writing code to process a magnatic tape file, one record at a time; you are just using SQL to do.

Since you did not post any DDL, the most important part of any SQL problem, we can only guess or make general remarks without keys, constraints, DRI, etc.

CREATE VIEW FoobarSummary (fookey, total_x, total_y, ...)
AS SELECT fookey,
(SELECT SUM(x)
FROM Floob
WHERE Floob.fookey = F1.fookey),
(SELECT SUM(y)
FROM Frammis
WHERE Frammis.fookey = F1.fookey),
...
FROM Foobar AS F1
WHERE ...

This trick assumes that the fookeys point to relatively small subsets in Floob and Frammis. There are several other ways to do this.

--CELKO--
Joe Celko, SQL Guru

TJ
Posting Yak Master

201 Posts

Posted - 2002-03-25 : 15:37:33

quote:
No, you need to create a VIEW

The tables that I'm updating are accessed via Crystal Reports on our web server by possibly 400+ people at a time. How will a view benefit our users? Wouldn't the hard data, although redundant, be quicker to access?

quote:
You should never write more than five cursor in your entire career

I've only written one, and that one will go away this summer when I have the time to rewrite it. The SQL Team has helped me find alternative ways to write the code I need.

quote:
Since you did not post any DDL, the most important part of any SQL problem, we can only guess or make general remarks without keys, constraints, DRI, etc.

I'll remember this in the future!

Thanks for your input!

Teresa

"Someday I'll know enough to help someone else!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources