Using and returning booleans - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Using and returning booleans

Author

Topic

nickjones
Starting Member

16 Posts

Posted - 2003-10-24 : 08:03:46

I do hope I'm being stupid here, but I'm having trouble with returning and using booleans in an SQL statement on SQL Server 97. For example, I can't even do;

SELECT (2>1)

or even

SELECT TRUE

These work just fine in access.

The actual query I need to re-write (which works in Access 97) is

SELECT (NOT (value1='somevalue') OR somevalue IS NULL) As MissMatch FROM mytable WHERE section = 'somesection'

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-24 : 08:13:35

CAST the result as TYPE BIT

SELECT CAST(expression AS BIT)

Bits can be CAST back into INTEGER as 0 or 1.

Sam

SamC
White Water Yakist

3467 Posts

Posted - 2003-10-24 : 08:20:50

A bit more BIT information in BOL:

CASTing any non-zero experssion to BIT results in 1.

Bit constants are represented as 0 or 1 without quotes.

In ADO, the parameter is passed as adBoolean

BITs can be recast to integer as CAST(bit AS INTEGER)

nickjones
Starting Member

16 Posts

Posted - 2003-10-24 : 08:34:29

quote:
Originally posted by SamC

CAST the result as TYPE BIT

SELECT CAST(expression AS BIT)

Bits can be CAST back into INTEGER as 0 or 1.

Sam

Thanks for the replies, but that doesn't seem to work. Doing;

SELECT CAST(2>1 As Bit)

still returns the error 'Incorrect syntax near '>'.'

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-24 : 08:38:12

Logical tests do not return a boolean value, as there is no such datatype in SQL Server. You can use CASE to evaluate an expression and return a value:

SELECT CAST(CASE WHEN 2>1 THEN 0 ELSE 1 END as bit)

nickjones
Starting Member

16 Posts

Posted - 2003-10-24 : 08:59:31

Thanks rob.
That seems like a bit of an oversight in SQL Server imo... why it couldn't just return a binary 0 or 1 based on a logical test seems silly to me, especially when Access handles it just fine.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-24 : 18:57:53

It's not an oversight, Access is doing it for convenience sake, and the truth is that it leads to sloppy programming (no offense intended). I've had more than one situation where an Access "shortcut" led to major logical errors, and they are A ROYAL BITCH to track down. SQL Server is conforming to a (higher) standard in programming logic by requiring an explicit evaluation and assignment. It also can't properly evaluate it the way Access does without having a true boolean datatype.

And big whoop, you wrap it into a CASE expression, it's not hard at all. If you wanted to have Access evaluate it as "Yes" or "No" you'd have to use an Iif() function anyway.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-25 : 11:45:20

In terms of computers, there should be no evaluation difference between any of these:

1 + 2
1 < 2
1 OR 2
1 AND 2

and so on ... those 4 things are exactly the same, and they are evaluated exactly the same. Just the OPERATORs are different. In most lanugages, all 4 are valid expressions and return a value and can be used anywhere.

For some reason, in SQL Server, they did not implement this -- they decided that if you include a BOOLEAN operator in an expression, then it becomes a boolean-only expression and at that point you can't return a value and it can only be used in certain places.

By the same token, where a boolean is expected, you can't use a normal expression. i.e., you can't say

SELECT * FROM tbl WHERE [Active?]

like you can do in other languages as well -- you must put a boolean in there. I do agree with Rob that there are some good things about this, it can make things more clear in many cases. But as someone who's programmed in many languages (including assembly) it does bother me that boolean expressions are treated differently in SQL Server from other expressions, when they internally are the exact same thing.

Just an implementation decision, I would guess.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-25 : 11:57:43

quote:
In terms of computers, there should be no evaluation difference between any of these:

1 + 2
1 < 2
1 OR 2
1 AND 2

and so on ... those 4 things are exactly the same, and they are evaluated exactly the same

Well....

SELECT * FROM myTable WHERE 1+2 ???
SELECT * FROM myTable WHERE 1<2 this will at least be evaluated
SELECT * FROM myTable WHERE 1 OR 2 ???
SELECT * FROM myTable WHERE 1 AND 2 ???

There is a difference between logical expression evaluation and a boolean value, even if that logical expression evaluatates to a boolean.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-25 : 12:38:54

again:

SQL Server makes a distinction between boolean and other expressions. MOst other languages and computers in general do not.

What is the definition of the WHERE clause in SQL ?

"only return rows in which the expression after the WHERE evaluates to TRUE"

(hopefully that's close enough)

Now, what is TRUE? in most languages, it is "not equal to 0".

Thus, the WHERE clause definiton now becomes:

"only return rows in which the expression after the WHERE evaluates to not equal to 0"

Thus, if T-SQL did NOT make the distinction between Boolean and other expressions (I can't think of another language that DOES -- can you?), then all 4 of the SQL statemetns you presented are perfectly valid.

1 + 2 = 3 = True, because 3 <> 0
1 < 2 = 1 = True, because 1 <> 0 (this is how computers do it internally of course)
1 OR 2 = True OR True = True (remember, this is not a bitwise OR)
1 AND 2 = True AND True = True (again, NOT a bitwise AND)

And so on ... again, I will state: SQL Server makes distinction between BOOLEAN and OTHER expressions, therefore you can't do this -- but it really doesn't HAVE TO make that distinction. It's just an implementation decision. Under the hood BOOLEAN and OTHER expressions all the same.

- Jeff

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-10-25 : 14:16:23

Jeff,

There are two different issues here:
* whether you can treat the results of boolean expressions as values.
* Whether you can use numeric values as predicates
(a) by making predicates have numeric values (the 'C' school)
(b) by having a boolean data type and an implicit conversion between numerics and booleans (the 'C++' school)).

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-25 : 14:27:01

quote:
1 + 2 = 3 = True, because 3 <> 0

You may program a computer to make sense of that, but it does not make any LOGICAL sense. Boolean math and logic existed long before computers did.

quote:
SQL Server makes a distinction between boolean and other expressions. MOst other languages and computers in general do not.

I'm not willing to jump off a cliff just because most other people might be jumping.

3 is neither true nor false, it is 3. 7-7 is not true or false, it is a mathematical operation that equals zero. Not equal to zero is simply not equal to zero; zero and one are numbers, not logical states. If other languages do not make that distinction, that makes them logically flawed. Here's a little snippet of JavaScript that demonstrates this:

if("AB"+"C") { alert("True"); } else { alert("False"); } ABC is true? Does that mean XYZ is false?
...even better...
if("False") { alert("True"); } else { alert("False"); }

"ABC" is never true or false, it is simply a string with the letters "ABC". The fact that the language may treat that as true does not make it true. A genuine boolean expression is one that can only be answered as true or false: "My cat has fleas", "The light bulb is on". To say:

quote:
Under the hood BOOLEAN and OTHER expressions all the same.

would mean that this can be considered a valid query:

SELECT * FROM myTable WHERE "ABC" ...or... SELECT * FROM myTable WHERE "AB" + "C"

And it is not. There is no logical test or comparison being made anywhere in that expression, therefore this expression is meaningless. To write a version of SQL that DOES answer that query, as written, would be seriously flawed. Not to mention the additional issue of nulls when performing logical tests; how is a null supposed to be true or false?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-25 : 16:26:11

We're talking numbers, not words ... i should have clarified that -- sorry. I thought when I indicated "true means not equal to 0" I implied that but I should have been more clear. And we know nulls are null -- to compare to a null or do math with a null results in Null.

seriously -- i don't have to get into computer science 101 here, do I ? I definitely respect your knowledge, but you are focusing too much on SQL Server as you know it....

The key thing, i think, is you are IGNORING what I said:

"True is defined as NOT EQUAL TO ZERO".

(again -- remember we are dealing with numbers only for this discussion)

So, let's try to answer this question:

"Is 3 + 1 True?"

With any boolean expression, as you mentioned, we should able to evaluate in that manner. for "My dog is red", we can "IS my dog red?" and so on.

so, let's do some replacement. We have defined true as <> 0, so let's replace that:

Is 3 + 1 not equal to 0 ??

Does that make more logical sense? Can you see the answer now?

Just because the expression isn't phrased in the way you or I would expect it to doesn't mean it CAN'T BE a boolean expression, especially when we define TRUE in such a manner.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-25 : 18:25:33

quote:
The key thing, i think, is you are IGNORING what I said

But it's OK when you do the same thing:

SELECT * FROM Northwind..Employees WHERE 1

Don't know WHY, but that query doesn't return ANYTHING, which can't be right because 1<>0, therefore it must be true, therefore I should get all the rows in that table back. Isn't that what you said? Guess SQL Server as I know it is really screwed up for requiring a proper logical condition to be evaluated in the WHERE clause, instead of just a value that is not false, I'm sorry, zero.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-25 : 19:43:54

As I stated, SQL Server requires a boolean expression in some places and does not allow non-boolean expressions in those places, and vice versa.

i.e., CASE WHEN bool THEN exp ELSE exp END

bool MUST be boolean. exp MUST be non-boolean. That is a SQL-Sever implemenation decision. same with a WHERE clause:

WHERE bool

With what you are trying to do -- IN SQL SERVER -- that expression gives a SYNTAX ERROR and does not execute, compile or evaluate ANYTHING because it requires a BOOLEAN expression (as defined by SQL Server) and you are not providing one.

ARRGGG !!!!!!

I know you understand the differnce between no data being returned because of a syntax error vs. no data being returned due to evaluation of a condition ... i hope !!!

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-25 : 23:40:59

quote:

Guess SQL Server as I know it is really screwed up for requiring a proper logical condition to be evaluated in the WHERE clause, instead of just a value that is not false, I'm sorry, zero.

and, again, it is an IMPLEMENTATION DECISION. not screwed up. not wrong. not right. just a choice when they wrote SQL server. Clearly, you are having trouble with this topic so I feel it's not really worth a debate.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-10-26 : 00:24:33

quote:
Clearly, you are having trouble with this topic so I feel it's not really worth a debate.

Clearly, you'd rather talk to me like I'm an idiot child instead of listening, and accuse me of doing the same thing to you. Instead of asking me to clarify my meaning, you just assume that I just don't get it and continue to make the same point over and over. Since you don't seem to want to bother with my example, perhaps Arnold's earlier comments are more palatable (don't know, you never responded to him) Don't let the fact that I agree 100% with him dissuade you from reading them.

You've maintained that "any value not equal to zero is true". The crucial point is the "not equal to" part, not the "zero" part. The EVALUATION returns true or false, not the VALUE. That is what I was trying to demonstrate with my example. Your explanation indicates this evaluation implicitly, while SQL Server requires it to be explicit. Considering the way this thread has been misunderstood by both parties, I think you might agree that explicit is better.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-26 : 06:44:33

Not much understanding what's discussed in this thread I dare
to mention that I'd be strongly opposite the possibility to write
in T-SQL like this:

SELECT * FROM Northwind..Employees WHERE 1

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-26 : 09:35:25

Rob -- how does Arnold's post help your argument and how does it contradict with anything that I've said?

quote:

The EVALUATION returns true or false, not the VALUE

I never said it returns THE VALUE -- I said it returns A VALUE. And nothing returns "true" or "false" -- they ARE NOT values, they are concepts. Internally SQL Server assigns them (just like Arnold mentions) a value. We don't know what, and we cannot do those conversions ourselves (without using CASE; and CASE is not a conversion function as you know) and that is all that I don't like personally.

I don't care if it has to be explicit or if it can be implicit, I would like to be able to manipulate boolean expressions in T-SQL which all other languages allow. It troubles me (as I mentioned -- that's called an opinion by the way) that there is this internal datatype that can never be converted to or from in T-SQL and is aparently handled differently from the rest.

End of story. Chill out, man ! enjoy the extra hour of daylight savings today and have some beers and watch some football.

- Jeff

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-10-26 : 18:09:33

All this pain, suffering and frustration proudly brought to by NULL...Now available on all data types.

DavidM

"SQL-3 is an abomination.."

nickjones
Starting Member

16 Posts

Posted - 2003-10-27 : 05:27:51

Wow, didn't expect to provoke such a debate.
I won't comment on the more complex aruguements here - not least because they go over my head - but I still think the was MS-SQL deals with booleans isn't 'standard'.

I program in many high-level languages. I every one I know, you can do the following (pseudocode follows);

intA = 4
intB = 8 / 2
blnEqual = (intA = intB)

I can't think of a language which doesn't allow this. It just makes sense. So I'm somwhat suprised that T-SQL in sql server doesn't allow it.

It also means my rather neat ASP code will have to be modified. Currently I've got various lines similar to (this is simplified, you get the idea);

blnGotValue = DataConn.Execute("SELECT (Field1="value") As GotValue From Table")("GotValue")

One line, nice and simple, I can read it easily, it's simple to understand.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources