Alex, You can't use a CF condition within your SQL that you want to evaluate each record in the query. For that, you'd need to use an SQL condition. You could use CF conditions inside your cfquery only to evaluate the 'text' of the query (like to decide WHICH SQL statement to use for example based on values that exist apart from your data).
You can use a 'isnull' function to solve this (this is a DATABASE function, not a CF function, and is, therefore, DB dependent). I'm not sure which DB you are using, but for SQL Server it's isnull. For Oracle it's NVL. <cfquery name="recipients" datasource="labels"> SELECT isNull(emailaddr1,emailaddr2) as emailaddr ... </cfquery> This says, "if emailaddr1 has a value, then return it as 'emailaddr', but if it is null, then return the value in emailaddr as 'emailaddr'. If emailaddr2 is also null, then 'emailaddr2' will be null. If you want another default value such as 'N/A', then you would just wrap it all with another function like this: SELECT isNull(isNull(emailaddr1,emailaddr2),'N/A') as emailaddr Hope this helps! Dave Phillips -----Original Message----- From: alex poyaoan [mailto:ap.cli...@tiscali.it] Sent: Wednesday, March 03, 2010 7:09 AM To: cf-talk Subject: problem with a condition cfif in a cfquery HI everybody I have a table with two email addresses for every person there are cases though that emailaddr1 is empty and emailaddr2 has a value. I need to output the addresses that if emailaddr1 is empty it would be substituted by emailaddr2. I tried using a cfif inside the query written below but doesn't work <cfquery name="recipients" datasource="labels"> SELECT <cfif (emailaddr1)eq "">emailaddr2, <cfelse> emailaddr1</cfif> </cfquery> <cfoutput>#recipients#</cfoutput> thanks ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:331303 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
