This should do it (depending on the db)... SELECT CASE WHEN field = 1 THEN 'YES' ELSE 'NO' END AS yesNoField
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 31, 2005 12:23 PM To: CF-Talk Subject: RE: Oracle SQL alternative for YesNoFormat() ? Bobby, This doesn't work for me because the values in the database are in a numeric column and stored as 1 or 0. I need the query to return 'yes' or 'no'. The query results are used for a CFINDEX, so the only option I can think of at this point is to build a NEW query and transform those values as I loop through the original query recordset. This will add alot of overhead to the process though. I can't do a PL/SQL function either, so that's not an option. Anyone else have any other ideas? Dave -----Original Message----- From: Bobby Hartsfield [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 3:40 PM To: CF-Talk Subject: RE: Oracle SQL alternative for YesNoFormat() ? I haven't messed with Oracle much but if you don't find a better solution, you can hack something up that returns the length of the value minus 2 which will return 1 for yes and 0 for no This should do it... select len(YesNoField)-2 as YesOrNo from MyTable if the values in that field are only yes or no, you will get 1 for yes and 0 for no it's a hack job I know but it works The ideal solution would be to change the field values but I know that isn't always feasible with a client-supplied database. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, August 29, 2005 3:14 PM To: CF-Talk Subject: SOT: Oracle SQL alternative for YesNoFormat() ? Hi, I know this is a bit off topic, but I developing an app in CF accessing an Oracle DB and I need my query to return the results in the format of 'yes' or 'no' for 1 or 0 respectively. Anyone know how I can do this within the SQL select statement? Thanks, Dave ************************************************************************ ****************** The information contained in this message, including attachments, may contain privileged or confidential information that is intended to be delivered only to the person identified above. If you are not the intended recipient, or the person responsible for delivering this message to the intended recipient, ALLTEL requests that you immediately notify the sender and asks that you do not read the message or its attachments, and that you delete them without copying or sending them to anyone else. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:217004 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
