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))
)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?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3433
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
