divide by zero error

Hello,
When I originally wrote the SQL below, I didn't think the last two fields
could ever be zero, but I was wrong. How can I rewrite this so if
NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
SELECT
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/ dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
Thanks in advance!
AmberTry:
SELECT
isnull(
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/
nullif(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION, 0) /
nullif(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE, 0)
, 0) AS TotWithFuel
AMB
"amber" wrote:

> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber|||"amber" <amber@.discussions.microsoft.com> wrote in message
news:50239A89-6D09-4DE2-805F-3A6E55FBEA4B@.microsoft.com...
> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber
SELECT
CASE WHEN dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL = 0 OR
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE = 0 THEN 0 ELSE
(dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/ dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE END AS TotWithFuel|||SELECT case when dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION = 0 or
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE
= 0 then 0
else (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
(dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
* dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
/
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE
end AS TotWithFuel
I would also consider using an alias on your tables to make it easier to
work with, at least to get rid of the dbo. in the column aliases. That
looks hard to read :)
just add AS <alias> like:
from dbo.tablename as tablename or something like this, but even shorter.
Don't lose the meaning of the name in the shortend version, thought. What
you have is better than using the kind of aliases some folks use: (A.? B.?
C.?) When you have like 10-15 tables in the query who knows what is going
on.
--
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"amber" <amber@.discussions.microsoft.com> wrote in message
news:50239A89-6D09-4DE2-805F-3A6E55FBEA4B@.microsoft.com...
> Hello,
> When I originally wrote the SQL below, I didn't think the last two fields
> could ever be zero, but I was wrong. How can I rewrite this so if
> NUM_PRODUCTION or NUM_PIECE_SIZE are zero, TotWithFuel simply equals zero?
> SELECT
> (dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_RATE +
> (dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_FUEL_RATE -
> dbo.TDT_CUT_BLOCK_LGN_RATE.NUM_BASE_FUEL_RATE)
> * dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_FUEL)
> / dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PRODUCTION /
> dbo.TDT_CUT_BLOCK_LGN_FALLING.NUM_PIECE_SIZE AS TotWithFuel
> Thanks in advance!
> Amber