Boolean expression not working

Question

Boolean expression not working

michael.leach2015

SSCommitted

Points: 1597
More actions
April 21, 2019 at 11:02 pm

#3505508
I have the following code:
```
SELECT A, B, A = B
FROM (SELECT ((.1 + .2) * 10) as A, (1.0 + 2.0) as B) as tbl1
```
which returns this:
Shouldn't the 3rd column be named A = B instead of just A? More importantly instead of having a value of 3.0 in the 3rd column, should the value be TRUE (or maybe FALSE)?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

SQL Server allows the use of = notation for a column alias. So you have an alias, not a Boolean expression. The column alias is A so it would be named A. Just as if you did using more standard SQL:

SELECT A, B, B as A
FROM (SELECT ((.1 + .2) * 10) as A, (1.0 + 2.0) as B) as tbl1

Sue

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 2

Ok. I didn't know that. Good to know. Thank you.

Jonathan Szeto SSC Enthusiast Points: 120 More actions · Answer 3

If you wanted the third column header to read as "A = B", then you have to type it as

[A = B]

If you want the third column to evaluate as a boolean expression, you have to use a CASE statement.

CASE
    WHEN A = B THEN cast(1 AS bit)
    ELSE cast(0 AS bit)
END

Note that in SQL, true and false are displayed as 1 and 0 respectively. It's not quite the same as the Boolean structure in .NET.

michael.leach2015 SSCommitted Points: 1597 More actions · Answer 4

Ok. If I use the CASE statement, could I still use that in my code above? Where would it go? Would it actually go in the SELECT statement? It would seem weird to put all that code in the SELECT statement.

Also what is the purpose of casting 1 and 0 to a bit data type? Is that what you are doing? If so, why not just leave the choices as 1 and 0 since that would be recognized as True or False?

Does SQL consider any number other than zero to be true, or could it be that only a 1 represents true?

GaryV Hall of Fame Points: 3307 More actions · Answer 5

In your select statement, you'd replace

, A = B

with

, CASE .... END

that was given above.

The Cast converts the constants 1 or 0 which are integer values to bit data types. Try it both ways (with and without the Cast) and see what you get.

Chris Wooding SSCarpal Tunnel Points: 4554 More actions · Answer 6

One advantage of casting 1 and 0 to bit is that some UIs will default to displaying these as True/False or ticked/unticked without any special coding.