RE: Query of Queries Empty String Issue

[Bobby Hartsfield]

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

Re: Query of Queries Empty String Issue

[Claude Schnéegans]

 >>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