Replace 0 or -1 with proper string representation on query 1

[Axoliien]

Confused on this one, I want to take the value given in a row (it is a bit value) and output a "Yes" or "No" instead of the representative 0 or -1 during a query.

Example:

SELECT AppName, Inactive
FROM ApplicantStatus
ORDER BY AppName


[b][COLOR=red]Current Output[/color][/b]
Name:
Billy Bob   -1
Joe Bob     0
Silent Bob  0

[b][COLOR=blue]Desired Output[/color][/b]
Name:
Billy Bob   Yes
Joe Bob     No
Silent Bob  No

Thanks!

 

[cLFlaVA]

SELECT AppName,
       CASE
         WHEN Inactive = -1 THEN 'Yes'
         WHEN Inactive = 0 THEN 'No'
       END As Output
FROM ApplicantStatus
ORDER BY AppName

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[jdgonzalez]

You might want to consider a case statement.

SELECT AppName, 
(case when Inactive = -1 then 'Yes' else 'No' end) as inactive
FROM ApplicantStatus
ORDER BY AppName

 

[Axoliien]

HA! I feel purty stupid

 

[cLFlaVA]

Lol. No reason to.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cLFlaVA]

By the way, jdgonzalez's example is more concise. You should always have an ELSE statement, although not required, in your CASE statements. Since your field is a bitwise field, there will only ever be two values (I hope), so that solution is best.

*cLFlaVA
----------------------------
Lois: "Peter, you're drunk!"
Peter: "I'm not drunk, I'm just exhausted from stayin' up all night drinking!

 

[cmmrfrds]

Can a bit field have a -1?

 

[donutman]

Hehe, good point cmmrfrds!
-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]

 

[SQLSister]

Bit fields have a value of -1 if they are in Access. When you use SQl Server tables as linked tables in Access, it automatically ocnverts the 1 to -1 for Access to read.

Questions about posting. See faq183-874