Using OR to select different fields 2

[4N6Mstr]

Hello all,

In MS Access, I can use OR inside a SELECT clause to show if one or more values in a serie of boolean fields is true. Something like

SELECT Table_I.Field_A, Table_I.Field_B, [Table_I.Field_C] OR [Table_I.Field_D] OR [Table_I.Field_E] AS MyBooleanResult FROM Table_I

What would be the equivalent syntax in MS SQL 2005?

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[gmmastros]

use the pipe character |

SELECT Table_I.Field_A, Table_I.Field_B, [Table_I.Field_C] [!]|[/!] [Table_I.Field_D] [!]|[/!] [Table_I.Field_E] AS MyBooleanResult FROM Table_I

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[4N6Mstr]

Works like a charm! Thank you very much, George!

Any help is always apreciated!

Thx,


4N6MSTR
______________________________________________
If you don't know where you are going
It does not matter how fast you are
You will never get there

 

[gmmastros]

You're welcome.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Hey George, that is cool. I never saw anything like that. I was thinking of using a CASE statment. What is that called and are there other boolean operations like that?

Thanks
Jim

 

[gmmastros]

It called a bitwise OR operator.

There are a couple others:

http://msdn.microsoft.com/en-us/library/ms188725.aspx

There is And, Or, Exclusive OR, Not.

These things come in pretty handy for "deciphering" bit packed status values. For example, if you have a series of true false values, you could create a bunch of bit columns in your table where each column means something. Or you could use an Int column and store 32 different yes/no pieces of data.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Excellent, thanks George

 

[gmmastros]

I should mention that SQL Server is known to do this on occasion. For example, there is a system view names sysindexes. It will show you what indexes exist in your database. There is a status column that uses this "bit packing" technique.

Select  Name,
        Status,
        Case When Status & 1 = 1 Then 'Y' Else '' End As IgnoreDuplicateKeys,
        Case When Status & 2 = 2 Then 'Y' Else '' End As [Unique],
        Case When Status & 4 = 4 Then 'Y' Else '' End As IgnoreDuplicateRows,
        Case When Status & 16 = 16 Then 'Y' Else '' End As [Clustered],
        Case When Status & 32 = 32 Then 'Y' Else '' End As Hypothetical,
        Case When Status & 64 = 64 Then 'Y' Else '' End As [Statistics],
        Case When Status & 2048 = 2048 Then 'Y' Else '' End As PrimaryKey,
        Case When Status & 4096 = 4096 Then 'Y' Else '' End As UniqueKey,
        Case When Status & 8388608 = 8388608 Then 'Y' Else '' End As AutoCreate,
        Case When Status & 16777216 = 16777216 Then 'Y' Else '' End As StatsNoRecompute
From     sysindexes

The code above does a good job of unpacking the bits.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jbenson001]

Yes, perfect sense.

Thanks again George.