CODEWITHSUNDEEP
sqlsql-server
jason
jason

Reputation: 7164

How to combine two nullable boolean fields in SQL

I have these fields :

 ISNULL(
    dbo.Features.TheifAlarm,
    0
) AS ExtraTheifAlarm,
 ISNULL(
    dbo.Features.FireAlarm,
    0
) AS ExtraFireAlarm,

dbo.Features.TheifAlarm and dbo.Features.FireAlarm are nullable bits. But I'm using EntityFramework and they are nullable boolean in my model. I want to combine them with OR.

This is my attempt : 

     ISNULL(
        dbo.Features.TheifAlarm,
        0
    )
OR
     ISNULL(
        dbo.Features.FireAlarm,
        0
    )

But it didn't work, how can I combine these two columns? Thanks in advance

Upvotes: 0

Views: 90

Answers (2)

Pred
Pred

Reputation: 9042

To 'combine' two fields you can use bitwise operators.

In your case (in the SELECT list):

SELECT (ISNULL(col1, 0) | ISNULL(col2, 0)) AS combinedCol FROM ...

Or as a WHERE condition:

SELECT ... WHERE (ISNULL(col1, 0) = 1 OR ISNULL(col2, 0) = 1)

Actually you don't even need the ISNULL() in the WHERE condition (col1 = 1 evaluates to FALSE (ok, NULL, but treated as FALSE) when col1 is null):

SELECT ... WHERE (col1 = 1 OR col2 = 1)

Edit: Just to add some alternatives:

Simple addition

SELECT IIF((ISNULL(col1, 0) + ISNULL(col2, 0)) > 0, 1, 0)

CASE..WHEN structure (as iamdave mentioned)

SELECT CASE WHEN col1 = 1 THEN 1 ELSE IIF(col2 = 1, 1, 0) END

Another CASE..WHEN

SELECT CASE WHEN (col1 = 1 OR col2 = 1) THEN 1 ELSE 0 END

or with IIF

SELECT IIF(col1 = 1 OR col2 = 1), 1, 0)

Upvotes: 2

iamdave
iamdave

Reputation: 12243

I am assuming you want to return a true if either field is true?

If so, you can check for both with a case:

select case when ISNULL(dbo.Features.TheifAlarm,0) = 0  -- If the first is False
            then ISNULL(dbo.Features.FireAlarm,0)       -- Return the value of the second
            else 1                                      -- Otherwise return True
            end as EitherAlarmCheck

If you are looking to filter your dataset for one of these values being True, you can use an OR in your where clause:

select Cols
from Table
where ISNULL(dbo.Features.TheifAlarm,0) = 1
   or ISNULL(dbo.Features.FireAlarm,0) = 1

Upvotes: 1

Related Questions