Hey SQL Gurus..... 3

[gbaughma]

Here's a simple one.

I have a table that I'm bouncing a query off of.

One of the fields is a "Bit"

In my query, I'd like it to return a "Yes" or a "No" instead of a -1 or a 0.

Something like "SELECT thebitfield AS strYesNo FROM thetable"

You get the idea... any thoughts? Thanks!



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[RiverGuy]

SELECT
strYesNo =
CASE
WHEN thebitfield = 0 THEN 'No'
ELSE 'Yes'
END
FROM theTable

 

[markros]

How about

SELECT CASE WHEN thebitfield = 1 THEN 'Yes' ELSE 'No' END as strYesNo FROM theTable

 

[AlexCuse]

In my query, I'd like it to return a "Yes" or a "No" instead of a -1 or a 0.

SQL Server stores bit columns as 1 and 0, is this an access table by any chance? If so, take a look at the IIf function. Something like

iif(BitColumn = 0, 'No', 'Yes')

If not, carry on

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[gbaughma]

*'s to all... Alex, that looks like just what I needed.

Obviously, my SELECT statement is a lot longer than my example... so that iif will embed nicely.



Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[gbaughma]

Ooopsies... should have read more carefully.

It is SQL, not Access.

Therefore, looks like I have to use the CASE statement instead....

Just my 2¢

"What the captain doesn't realize is that we've secretly replaced his Dilithium Crystals with new Folger's Crystals."

--Greg

http://parallel.tzo.com

 

[AlexCuse]

Sorry, I should have capitalized it.

The -1 and 0 threw me off. Glad you got it working though!

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page