Query of Queries Empty String Issue
I have the following query of queries query SELECT code, title FROM Nav WHERE nav = 'Calendar' AND order = '' It has recently stopped returning results when upgrading to CF9. If I change the where clause to the following it returns the record WHERE nav = 'Calendar' AND order IS NULL Any ideas why the original where clause stopped working? I thought CF treated all NULL value fields in a record set as an empty string. Donnie ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346455 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Query of Queries Empty String Issue
Any ideas why the original where clause stopped working? I thought CF treated all NULL value fields in a record set as an empty string. probably the CF9 code is more consistent with SQL. Right, if a field is returned NULL in a query, CF treats it as an empty string, but apparently, the NULL state of the column is now preserved and transmitted to QoQ, which is logical after all. I think that column types are also preserved now. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346459 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
RE: Query of Queries Empty String Issue
NULLs are actually NULL now (what a concept, right?). If possible, I'd filter the nulls out on the main (real) query... if not, I'd probably use this instead: AND (order is NULL or order = '') .:.:.:.:.:.:.:.:.:.:.:.:.:. Bobby Hartsfield http://acoderslife.com http://cf4em.com -Original Message- From: Donnie Carvajal [mailto:donnie.carva...@transformyx.com] Sent: Tuesday, August 02, 2011 11:32 AM To: cf-talk Subject: Query of Queries Empty String Issue I have the following query of queries query SELECT code, title FROM Nav WHERE nav = 'Calendar' AND order = '' It has recently stopped returning results when upgrading to CF9. If I change the where clause to the following it returns the record WHERE nav = 'Calendar' AND order IS NULL Any ideas why the original where clause stopped working? I thought CF treated all NULL value fields in a record set as an empty string. Donnie ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:346467 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
