Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hey SQL Gurus..... 3

Status
Not open for further replies.

gbaughma

IS-IT--Management
Staff member
Nov 21, 2003
4,772
US
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
 
SELECT
strYesNo =
CASE
WHEN thebitfield = 0 THEN 'No'
ELSE 'Yes'
END
FROM theTable
 
How about

SELECT CASE WHEN thebitfield = 1 THEN 'Yes' ELSE 'No' END as strYesNo FROM theTable
 
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

Code:
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
 
  • Thread starter
  • Moderator
  • #5
*'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
 
  • Thread starter
  • Moderator
  • #6
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top