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)
 DIVIDE BY ZERO

Author  Topic 

Ticks
Starting Member

10 Posts
Posted - 2005-12-13 : 17:43:31
I am having trouble with an UPDATE trigger containing a select subquery. I have posted below an extract of the code that doesn't seem to work. You will see that I have tried to isolate a divide by zero error however when stock.qty - deleted.qty + inserted.qty = 0 the trigger still fires and tries to calculate the average cost? Please help my eyes are smarting!
UPDATE tblStock
tblStock.CostPrice = ((tblStock.Qty * tblStock.CostPrice)+
(SELECT SUM((inserted.STKQty * inserted.CostPrice) -
(deleted.STKQty * deleted.CostPrice))
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO)) /
(tblStock.Qty +
(SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO))
WHERE tblStock.PartCode IN
(SELECT tblStock.PartCode FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)
AND tblStock.Type IN
(SELECT tblStock.Type FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0) AND tblStock.SiteID IN
(SELECT tblStock.SiteID FROM
(tblStock INNER JOIN inserted ON
(tblStock.Type = inserted.Type)
AND (tblStock.PartCode = inserted.PartCode)
AND (tblStock.SiteID = inserted.SiteID_To))
INNER JOIN deleted ON
(tblStock.PartCode = deleted.PartCode)
AND (tblStock.Type = deleted.Type)
AND (tblStock.SiteID = deleted.SiteID_To)
WHERE ((inserted.Stock)=1)
AND (tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)


Needs Help

Kristen
Test

22859 Posts
Posted - 2005-12-13 : 18:42:04
Can you just change:

(tblStock.Qty + 
(SELECT SUM(inserted.STKQty - deleted.STKQty) 
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO))

to use:
NullIf(tblStock.Qty + (...), 0.00)
in order to prevent a DivideByZero??

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-12-14 : 00:32:40
What do you want to do if the devisor is zero?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 00:56:47
quote:
Originally posted by Kristen

Can you just change:

(tblStock.Qty + 
(SELECT SUM(inserted.STKQty - deleted.STKQty) 
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO))

to use:
NullIf(tblStock.Qty + (...), 0.00)
in order to prevent a DivideByZero??

Kristen
No this will not work because the divide by zero error arrives when (tblStock.Qty - deleted.STKQty + inserted.STKQty) = 0 not when an ISNULL situation arrives. Therefore I was going to use 2 x triggers one coping with the case where the stkQty becomes zero and another to cope with stkQty <> 0. Hence in the example shown I don't want the trigger to fire if "(tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0)" but this doesn't seem to stop it, I think it is because the inner subquery calculates prior to the outer query therefore the error arrives prior to the <>0.

How can I exclude lines where (tblStock.Qty +
(SELECT SUM(inserted.STKQty - deleted.STKQty) is zero? in the following lines of my subquery

" /
(tblStock.Qty +
(SELECT SUM(inserted.STKQty - deleted.STKQty)
FROM inserted, deleted
WHERE tblStock.Partcode = inserted.Partcode
AND tblStock.Type = inserted.Type
AND inserted.TPOLineID = deleted.TPOLineID
AND tblStock.PartCode = deleted.PartCode
AND tblStock.Type = deleted.Type
AND tblStock.SiteID = deleted.SiteID_TO
AND tblStock.SiteID = inserted.SiteID_TO))"

Eyes still stinging!






Needs Help
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 01:09:38
"not when an ISNULL situation arrives"

I didn't use ISNULL(), I used NullIf()

Kristen
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 01:17:15
quote:
Originally posted by madhivanan

What do you want to do if the devisor is zero?

Madhivanan

Failing to plan is Planning to fail


The whole idea of the trigger is to maintain an avergae cost price for the unique (Partcode,Type,SiteID). However when the quantity is zero I want the cost price to remain as the last known price. Therefore in answer to your question when the devisor is zero I do not want to update the Cost Price.



Needs Help
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 01:23:33
quote:
Originally posted by Kristen

"not when an ISNULL situation arrives"

I didn't use ISNULL(), I used NullIf()

Kristen


I did say my eyes are smarting!

Sorry could you please explain how NULLIF will asist me?



Needs Help
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 02:08:56
ANY IDEAS ANYONE?

I really do need your assistance.

Needs Help
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 02:17:33
"could you please explain how NULLIF will asist me?"

I think that it will prevent the divide by zero error.

Try it?
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 02:49:13
quote:
Originally posted by Kristen

"could you please explain how NULLIF will asist me?"

I think that it will prevent the divide by zero error.

Try it?


It prevents the divide by zero error however it returns NULL value for tblStock.CostPrice = , and this is not possible.

Any other ideas?

I tried grouping within the subquery but the trigger updates multiple times when multirow with same (PartCode,Type,SiteID). Meaning to say
transaction
Partcode X, Type 1, Site 1 Change = 1
Partcode X, Type 1, Site 1 Change = 1

Then my quantity figure on the stock file reduces by 4.







Needs Help
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-12-14 : 03:08:28
Try this

IsNull(NullIf(tblStock.Qty + (...), 0.00),1)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 03:19:41
How is 1 a valid answer for divide by zero? It will be confused with real data of that value.

The data causing the zeros has to be excluded or fixed, or any such data should be converted to "NULL" - i.e. "Unknown" - in the results.

Or am I missing something?

Kristen
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 03:39:09
quote:
Originally posted by madhivanan

Try this

IsNull(NullIf(tblStock.Qty + (...), 0.00),1)

Madhivanan

Failing to plan is Planning to fail


I would end up with a corrupt cost price based on

(stockqty * costprice) + (inserted.qty x inserted.Costprice) - (deleted.qty x deleted.costprice) / 1

HOWEVER I could use

IsNull(NullIf(tblStock.Qty + (...), 0.00),0) thereby making the costprice zero and then have another UPDATE trigger in the stock file that says if qty = zero then costprice = deleted.cost.price

A bit messy and may have other conseqences using another trigger however worth considering.

I would really like to isolate the
(tblStock.Qty - deleted.STKQty + inserted.STKQty) <> 0

if this can be achieved then I can write another update for
(tblStock.Qty - deleted.STKQty + inserted.STKQty) = 0 excluding the costprice update.

Your advice on this is welcome.







Needs Help
Go to Top of Page

Ticks
Starting Member

10 Posts
Posted - 2005-12-14 : 03:43:19
quote:
Originally posted by Kristen

How is 1 a valid answer for divide by zero? It will be confused with real data of that value.

The data causing the zeros has to be excluded or fixed, or any such data should be converted to "NULL" - i.e. "Unknown" - in the results.

Or am I missing something?

Kristen


No I don't think you are missing anything. Like you say, I would really like to isolate the data causing the zeros and handle them on their own. However I can not get a script to work. Please see my previous post.



Needs Help
Go to Top of Page

Kristen
Test

22859 Posts
Posted - 2005-12-14 : 04:26:30
Can you put a CASE statement on it, so when it is Zero it is coerced to deleted.CostPrice?

Alternatively, could you extract the data from INSERTED and DELETED into a temporary table first, and then JOIN to that for the UPDATE - might give you a bit more control over the values involved.

We do this for Stock Level type stuff using a FULL OUTER JOIN so we have all PKs from INSERTED and DELETED represented in our Temp Table.

Here's a synthetic example of something that calculated the Quantity "Movement", or "Change", for a given bunch of PKs in a trigger:

INSERT INTO @MyTable
(
	MyPK_ID
)
SELECT DISTINCT
	COALESCE(I.MyPK_ID, D.MyPK_ID)
FROM	inserted I
	FULL OUTER JOIN deleted D
		ON  D.MyPK_ID = I.MyPK_ID

UPDATE	U
SET
	-- Quantity "movement"
	[MyQty]	= COALESCE(I.MyQty, 0)
		- COALESCE(D.MyQty, 0)
FROM	@MyTable AS U
	LEFT OUTER JOIN deleted D
		 ON D.MyPK_ID = U.MyPK_ID
	LEFT OUTER JOIN inserted I
		 ON I.MyPK_ID = U.MyPK_ID
-- Only update if the Qty has changed
WHERE	COALESCE(I.MyQty, 0) <> COALESCE(D.MyQty, 0)

Kristen
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site