> -----Original Message----- > From: Flesher, Rob [USA] [mailto:[EMAIL PROTECTED] > Sent: Friday, December 28, 2007 12:29 PM > To: CF-Talk > Subject: Query of Queries Issue > > All, > > Please forgive me if you get this twice, as it did not appear to post > the first time. > > I am having an issue with cold fusion query of queries when upgrading > to > MX7. Previously, when doing a SUM(querycol) in a query of queries when > all the values of the column were null, the sum would return a 0. In > MX7, it is returning a blank string, causing all calculations to error > out. > > Can someone tell me if CF query of queries has a function similar to > oracle's NVL? Basically, what I need to do is NVL(SUM(querycol), 0) to > insure any null values return as a 0. > > If a function like this doesn't exist, how would you recommend I deal > with this issue?
Well - I'm sure you could do an "if" outside the query (you can modify queries manually like any other data structure) but I assume you'd prefer something in the query itself. I'm not sure but can you do math in the query? Something like " SUM(querycol) + 0" - that should (if it's allowed trigger CFs automatic type conversion to create a number for you). For that matter having a zero in any of the rows should also do it... perhaps adding a dummy row with a zero value into the original query might be possible? Something to try at least. Jim Davis ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:295490 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4