True/False to Yes/No

[quinnipiac0807]

Is there a way to change the data coming out of a database using an sql statement. For instance, I have a column that's bit. Instead of it saying true/false when it comes up on my page i want it to say yes/no. How do I do this with a sql statement?

 

[donutman]

Normally you would to that in the app, but it is possible to use a case statement in a SQL query to change the bit value column to a varchar(3) to hold the constants 'yes' or 'no'.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[krisplus5]

You can use a CASE statement in your select (or perhaps create a view for ease of use and speed):

select id, title, bitfield, yesno=CASE bitfield when 1 then 'Yes' else 'No' end  
from table1

Hope this helps,
Kris

 

[vongrunt]

This is OK if bitfield cannot contain NULLs. Otherwise it will consider NULL as "No" which depending on app logic sometimes may be completely wrong.

 

[krisplus5]

Yes, quite true on the NULL issue. But could you simply add another WHEN clause (and turn some things around)? Such as:

select id, title, bitfield, 
yesno=CASE 
    when bitfield=1 then 'Yes' 
    when bitfield IS NULL then NULL 
    else 'No' end  
from table1

I haven't tried this, just shooting in the dark...

Cheers,
Kris

 

[donutman]

Yes that should work and you could even do this:

[Blue]SELECT[/Blue] id[Gray],[/Gray] title[Gray],[/Gray] bitfield[Gray],[/Gray] 
       yesno[Gray]=[/Gray][Blue]CASE[/Blue] bitfield 
                  [Blue]WHEN[/Blue] 1 [Blue]THEN[/Blue] [red]'Yes'[/red] 
                  [Blue]WHEN[/Blue] 0 [Blue]THEN[/Blue] [red]'No'[/red] [Blue]END[/Blue]  
   [Blue]FROM[/Blue] table1

The null should print on its own.
-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

hehe...u said dat!