CODEWITHSUNDEEP
sqlsql-server
Teson
Teson

Reputation: 6736

evaluated boolean return value in mssql - possible?

I'm trying:

select tbl.*, (true and true) as booleanTest
from tbl

which ms-sql doesn't seem to appreciate. (Incorrect syntax near the keyword 'and')

Blissed by ignorance I thought this was standard ANSI-something.

Q: Can the above be expressed in a more universal sql syntax?

Upvotes: 2

Views: 4166

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

SQL Server does not support standard SQL's BOOLEAN data type, remembering it is a very strange kind of Boolean that has three values! In place of BOOLEAN literal values you must substitute expressions that evaluate to the required truth value e.g. 

( 1 = 1 )      -- TRUE
( 0 = 1 )      -- FALSE
( 1 = NULL )   -- UNKNOWN

I usually write code like this (following your example):

SELECT tbl.*, 
       CASE 
          WHEN     ( ( 1 = 1 ) AND ( 1 = 1 ) ) THEN 'TRUE'
          WHEN NOT ( ( 1 = 1 ) AND ( 1 = 1 ) ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result
  FROM tbl;

As @Diego alludes (I think!), in conventional logic we could apply the idempotence rewrite rule, which states that

( P AND P )   <= is equivalent to =>   ( P )

As it happens, the rule holds for SQL's three valued logic.

e.g. proof in SQL Server for the case ( UNKNOWN AND UNKNOWN ) <=> UNKNOWN:

SELECT CASE 
          WHEN     ( 1 = NULL) AND ( 1 = NULL ) THEN 'TRUE'
          WHEN NOT ( 1 = NULL) AND ( 1 = NULL ) THEN 'FALSE'
          ELSE 'UNKNOWN'
       END AS result

Upvotes: 4

Diego
Diego

Reputation: 36126

what do you expect to achieve? There is no boolean on SQl, so you can do something like this:

declare @a bit
declare @b bit

set @a=0
set @b=1

select  
    case
        when  @a=@b then 1 
        when  not @a=@b  then 0
        else null
    end as Bool_test

possible results:

@a              @b        Result
0               0           1
0               1           0
1               0           0
1               1           1
NUll         any_value     NULL
any_value       NULL       NULL

Upvotes: 1

Related Questions