Whilst writing a ColdFusion based query to create a JSON response I thought I'd look up returning data values as a "Yes/No" string rather than "1/0". Then I wouldn't have to transform it in any way to use in the JSON build script.
The mySQL version allows you to do this by evaluating the value, and matching a string if the condition is met, like this:
SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
This does not work in ColdFusion at all. An error is thrown:
After a little tweaking it seems that if you alias the field it does work. In the example code below I've simply aliased the field with its own name.
I'm not exactly clear why, as the error message above isn't all that helpful.
SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive