update stored Proc returns null

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 2008 Forums

Transact-SQL (2008)

update stored Proc returns null

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-15 : 22:41:28

I am updating a table called tblRouteCashSalesHeader by summing values in the related detail table, tblRouteCashSalesDetail.
Routecashsalesheader has a field for bananas total and produce total . The way to determine if the product is a banana type or not (the productId is stored in the detail table) is to check a boolean in the product table: IsBananas. Here is what I have which works fine if there are bananas and non-banana products.

update tblRouteCashSalesHeader
set BananasTotalBoxes = (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 1)),

ProduceTotalBoxes = (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID =2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0))

where tblRouteCashSalesHeader.RouteCashSalesHeaderID = 2

-------------------------------

The problem is that if there are no bananas or if no produce, the select sum(boxes) returns a null value and the procedure crashes. I tried using isnull (see the case statement) but I still get null.

(what I tried)
update tblRouteCashSalesHeader
set ProduceTotalBoxes =
case when (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0)) = null then 0
else
(select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0))

end

What can I do?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-15 : 23:39:45

[code]
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal
FROM tblRouteCashSalesHeader h
inner join (
select RouteCashSalesHeaderID ,
sum(case when isbananas = 1 then boxes else 0 end) AS BananaTotal,
sum(case when isbananas = 0 then boxes else 0 end) AS ProduceTotal
from tblRouteCashSalesDetail
where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2
group by RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-16 : 10:45:42

Thanks for your help. I see from your answser that I did not make clear the table structures.

tblRouteCashSalesHeader has bananatotalboxes and producetotalboxes.
The tblroutecashsalesdetail has productID and boxes. Each detail record will have a product ID and the number of boxes.

The tblproduct has productID and isbananas, a boolean.

A product is either a banana or not a banana item(a produce item). So all banana items are totaled in the bananaboxtotal and everything not a banana goes into the producetotalboxes of the header table.

so the summing has to reference the product table which is why, I guess, I am having the problems. I could add the field "isbananas" to the detail table and then there would be no problem. Is that the best way to handle this? I know it is the easiest.

so the 3 tables and their fields:

1. Header table
HeaderID
bananatotalboxes
producetotalboxes

2. detail table
DetailID
HeaderID
boxes
productID

3. tblProduct
productID
Isbananas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 11:31:43

its just a matter of additional join


update h 
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal 
FROM tblRouteCashSalesHeader h
inner join (
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal 
from tblRouteCashSalesDetail  sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2 
group by sd.RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-16 : 14:54:18

Thank you very much for this solution. It works perfectly. I also needed a sum of the totals for the header field "TotalBoxes" so I added:

update h
set h.BananasTotalBoxes = d.BananaTotal,
h.ProduceTotalBoxes = d.ProduceTotal,
h.TotalBoxes = d.BananaTotal + d.ProduceTotal -- this was added for the total
from ......

This also works. I could not see any other way offhand to get the total of the 2 detail fields. Maybe there is a problem if both are null, but then I presume the totalboxes would be zero.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-16 : 22:08:16

[code]
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal,
h.TotalBoxes = d.Total
FROM tblRouteCashSalesHeader h
inner join (
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal ,
sum(sd.boxes) as Total
from tblRouteCashSalesDetail sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2
group by sd.RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-18 : 14:24:21

Thank you again. But here is a question. It seems that the 'd' in this query functions a bit like a separate table. Where can I find rules and examples on this technique and when is best to use it. This probably could be done with table varialbes but here it is not needed.

d:
(
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal ,
sum(sd.boxes) as Total
from tblRouteCashSalesDetail sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2
group by sd.RouteCashSalesHeaderID
)d

Here is another similar one (also a solution from this site and again, something called MaxTable has been created and is manipulated as a table. I would love to see someone do an article on this or if one has been done on these techniques, to post the reference. Thanks.

WITH MaxTable ( positionID, effectivedate)
AS

( select positionID, max(effectivedate) AS MAXDate
from tblBudgetMaster
WHERE effectivedate <= CONVERT(VARCHAR,@edate,1)
group by positionID
)
insert into tblbudgetmasterselected(BudgetMasterID,positionID,ProjectID, EffectiveDate, FiscalYear,
BudgetedSalaryRate, AnnualSalary, FTE)
(SELECT t.BudgetMasterID, t.positionID, 0, CONVERT(VARCHAR,t.EFFECTIVEDATE,101) AS EFFECTIVEDATE, FiscalYear,
BudgetedSalaryRate, AnnualSalary, FTE
FROM tblBudgetMaster t INNER JOIN MaxTable m ON t.positionID = m.positionID
AND t.effectivedate = m.effectivedate)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-18 : 16:41:28

former one is called derived table and latter technique common table expressions

see more details here

http://msdn.microsoft.com/en-us/magazine/cc163346.aspx#S1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

smh
Yak Posting Veteran

94 Posts

Posted - 2012-07-19 : 11:20:52

Thanks. This is very helpful indeed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources