Prev: Code and Creation 60240
Next: Problem with DECLARE CURSOR
From: Jack on 15 Jan 2010 15:56
Hi,
I have the following formula which is given the above errorl

ROUND(((orders.qty / finished_parts.psets) * finished_parts.pqot_cyc) +
finished_parts.setuphrs, 2) AS EstRunHrs1


I would appreciate any help for resolution of the above. Thanks.
From: John Bell on 15 Jan 2010 16:28

"Jack" <Jack(a)discussions.microsoft.com> wrote in message
news:D5A2E6F9-F718-4032-BDC9-495E83204976(a)microsoft.com...
> Hi,
> I have the following formula which is given the above errorl
>
> ROUND(((orders.qty / finished_parts.psets) * finished_parts.pqot_cyc) +
> finished_parts.setuphrs, 2) AS EstRunHrs1
>
>
> I would appreciate any help for resolution of the above. Thanks.

Hi Jack

It will depend on what you want to do when psets are 0?


ROUND(((orders.qty / NULLIF(finished_parts.psets,0)) *
finished_parts.pqot_cyc) + finished_parts.setuphrs, 2) AS EstRunHrs1

You could then use ISNULL to determine a value when finished_parts.psets
zero or any one of the values is NULL. To explicitly cater for
finished_parts.psets you could use CASE e.g.

CASE WHEN finished_parts.psets = 0 THEN 0
ELSE
ROUND(((orders.qty / finished_parts.psets) * finished_parts.pqot_cyc) +
finished_parts.setuphrs, 2)
END AS EstRunHrs1

If you want to ignore rows when finished_parts.psets is NULL or zero then
add a WHERE clause to exclude them:

WHERE finished_parts.psets <> 0

John

From: SQLMenace on 16 Jan 2010 09:16
Take a look at SQL Server efficient handling of divide by zero
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-efficient-handling-of-divide



"Jack" <Jack(a)discussions.microsoft.com> wrote in message
news:D5A2E6F9-F718-4032-BDC9-495E83204976(a)microsoft.com...
> Hi,
> I have the following formula which is given the above errorl
>
> ROUND(((orders.qty / finished_parts.psets) * finished_parts.pqot_cyc) +
> finished_parts.setuphrs, 2) AS EstRunHrs1
>
>
> I would appreciate any help for resolution of the above. Thanks.

 | 
Pages: 1
Prev: Code and Creation 60240
Next: Problem with DECLARE CURSOR