Adam, Not to throw a monkey wrench into the works here, but have you considered doing this in a sql stored procedure and calling the procedure from cf passing any parameters needed.
This way all your error handling could be handled inside sql, allowing you to do all kinds of variable error checking, expected length, data type etc. The sql sproc is compiled and is much faster than an adhoc select. Jim -----Original Message----- From: Adam Parker [mailto:[email protected]] Sent: Monday, February 27, 2012 7:27 AM To: cf-newbie Subject: Re: Multiplying Variables I ended up calculating the paddedLeadTime in the query: select cast(p.partdescription as varchar(100)) as partdescription, n.partnum , sum(isNull(n.leadTime,0)+21) as 'paddedLeadTime' ,sum(isNull(b.onhandqty,0)) as CurrentInventory, w.allocqty , sum(isnull(s.quantity,0)) as staged , sum(isNull(b.onhandqty-s.quantity, 0)) as AvailableInventory from [server].table.dbo.north_parts n with(nolock),partwhse w with(nolock), part p with(nolock) left outer join [server].table.dbo.staged_inventory s with(nolock) on s.partnum = p.partnum left outer join partbin b with(nolock) on b.partnum = p.partnum where p.company = 'PC68300' and w.warehousecode = 100 and p.partnum = n.partnum and w.partnum = n.partnum group by cast(p.partdescription as varchar(100)), n.partnum, w.allocqty, n.leadTime and incorporated the isNull function. I also incorporated the logic you suggested: <cfif getParts.recordCount and avgStoreData.recordCount> <!---calculate low water mark---> <cfset varA = #getParts.paddedLeadTime#*#avgStoreData.avgNum#> <cfelse> <cfset varA = 0> </cfif> This returns data. I thought the isNull function would be unnecessary with the sum function as sum ignores empty fields. Am I incorrect? Thanks again. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:5807 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-newbie/unsubscribe.cfm
