2010

Returning values from mySQL in a select query as Yes/No rather than 1/0

Posted By : Shaun McCran
Related Categories: Coldfusion, SQL

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:

view plain print about

1SQL version:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, "Yes", "No")
3FROM table.users

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.

view plain print about

1CF version:
2SELECT intid,varuserfname,varusersname, IF(intactive<>0, 'Yes', 'No') as intactive
3FROM table.users

Comments (10) |

Send |

del.icio.us |

Digg It! |

Linking Blogs |

Answer Questions

TweetBacks

Comments (Comment Moderation is enabled. Your comment will not appear until approved.)

[Add Comment] [Subscribe to Comments]

I've seen that error many times before :) In my experience, any time I have an error that says "not indexable by", it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:

#yourQuery.intactive#

... and the "intactive" column didn't exist. This would make sense in the first demo since you're not aliasing a column, but rather, creating a completely new, calculated column based on the intactive column. If you dump out the query result, it probably gave it some crazy column name.

For example, if I run the query:

select IF( id = 3, 'a', 'b' ) from blog_entry LIMIT 5

... the column name comes back as "IF( id = 3, 'a', 'b' )"... funky!

So anyway, that's why the aliasing is crucial.

# Posted By Ben Nadel | 12/03/2010 22:25

# Posted By dr david caminer plastic surgeon | 03/10/2015 07:04

# Posted By sydney seo company | 12/10/2015 02:34

I have an error that says "not indexable by", it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:

# Posted By Build My List 20 | 05/11/2015 23:14

, it means that I tried to access a column name that didn't exist (typically because I misspelled it). You probably tried to do:

# Posted By taxi for delhi jaipur | 30/11/2015 04:03

this wonderful post that you have provided for us. I assure this would be beneficial for most of the people.

# Posted By buy instagram likes | 18/12/2015 21:41

I want you to thank for your time of this wonderful read!!! I definitely enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog....

# Posted By cccam server | 18/12/2015 23:47

Trying different ways is always noisy but gives innovative thoughts. Returning values as YES and NO rather than binary is sounding cool, and well explained just like i have referred from http://pay4essayonline.blogspot.com/2015/12/avoid-... . The information is good and please give some more examples. Please give as an information on this purpose so that it would be more helpful.

# Posted By gibbson | 21/12/2015 01:26

It's my first time i visit here. I found so many entertaining stuff in your blog, especially its discussion. From the tons of comments on your articles...

# Posted By cccam server | 22/12/2015 02:38

I want you to thank for your time of this wonderful read!!! I definitely enjoy every little bit of it and I have you bookmarked to check out new stuff of your blog a must read blog....

# Posted By cccam server | 22/12/2015 04:02

[Add Comment]

Shaun Mccran

Returning values from mySQL in a select query as Yes/No rather than 1/0