CODEWITHSUNDEEP
sqldivide-by-zero
aparker81
aparker81

Reputation: 263

NullIf() to Prevent Divide by Zero

In my query, when calculating the average, I encounter a divide by zero error. I am attempting to resolve this by using Nullif, but I don't think my syntax is correct as Coldfusion throws an error stating incorrect syntax near ')'.

My query is:

<cfquery name="getValueAdd" datasource="#myDSN#">
    select d.partnum, sum(docunitprice * orderqty) as total_sales, 
    sum((c.avglaborcost + c.avgburdencost + c.avgmaterialcost + c.avgsubcontcost + c.avgmtlburcost)*d.orderqty) as total_cost,
    sum((docunitprice * orderqty)-((c.avglaborcost + c.avgburdencost + c.avgmaterialcost + c.avgsubcontcost + c.avgmtlburcost)*d.orderqty)) as Value_add,
   avg (isNull(
((((docunitprice * orderqty)-((c.avglaborcost + c.avgburdencost + c.avgmaterialcost + c.avgsubcontcost + c.avgmtlburcost)*d.orderqty))/ (nullIf(docunitprice * orderqty), 0),0)
))) as PercValueAdd
    from orderhed h with(nolock), orderdtl d with(nolock), partcost c with(nolock)
    where h.company = 'PC68300'
    and d.company = h.company
    and c.company = h.company
    and d.ordernum = h.ordernum
    and c.partnum = d.partnum
    and hdcasenum =  <cfqueryparam cfsqltype="cf_sql_integer" value="#rc.hdcasenum#" />   
    group by d.partnum
</cfquery>

Can anyone clarify the syntax for me please?

Upvotes: 1

Views: 8382

Answers (1)

MoMo
MoMo

Reputation: 8200

NullIf() takes two parameters. Did you search for the NullIf() documentation?

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

Here is an example: http://www.bennadel.com/blog/984-Using-NULLIF-To-Prevent-Divide-By-Zero-Errors-In-SQL.htm

Upvotes: 3

Related Questions