June 19, 2018 at 6:34 am
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
June 19, 2018 at 6:50 am
Your question lacks all the basics of asking good questions here.
I will attempt to answer you anyway, but please supply DDL and sample data for us to assist you better.
In SQL Server, it is known as BIT data type, 1 is converted to TRUE, 0 to FALSE.
So a basic query would look something like:
select columnname from table where bitcondition_column = 1
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
June 19, 2018 at 10:57 am
DECLARE @t table(Id int IDENTITY PRIMARY KEY, bit1 bit, bit2 bit, bit3 bit, bit4 bit, bit5 bit, bit6 bit, bit7 bit, bit8 bit);
INSERT @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8)
VALUES (0, 1, 0, 0, 1, 0, 0, 0), (0, 0, 1, 0, 0, 1, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0), (1, 0, 0, 0, 1, 0, 0, 0)
, (0, 1, 0, 0, 1, 0, 0, 0), (0, 1, 1, 0, 0, 0, 0, 0), (1, 0, 0, 0, 0, 0, 1, 0), (1, 0, 0, 0, 1, 0, 0, 0);
SELECT
t.Id,t.bit1,t.bit2,t.bit3,t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
FROM
@t t
WHERE Cast(t.bit1 AS int) + Cast(t.bit2 AS int) + --Find rows with no bits set
Cast(t.bit3 AS int) + Cast(t.bit4 AS int) +
Cast(t.bit5 AS int) + Cast(t.bit6 AS int) +
Cast(t.bit7 AS int) + Cast(t.bit8 AS int) = 0;
--Find rows with any bits set
SELECT t.Id, t.bit1, t.bit2, t.bit3, t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
FROM
@t t
WHERE Cast(t.bit1 AS int)
+ Cast(t.bit2 AS int)
+ Cast(t.bit3 AS int)
+ Cast(t.bit4 AS int)
+ Cast(t.bit5 AS int)
+ Cast(t.bit6 AS int)
+ Cast(t.bit7 AS int)
+ Cast(t.bit8 AS int) > 0;
June 19, 2018 at 11:31 am
Personally, i would go with a bunch of ORs or an IN. Using Joe's Sample Table:
Casting the values (to an int) might cause the query to become non-SARGable; so if you have a lot of rows (not records, SQL Server doesn't have records) and any indexes on those columns you might find these faster.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 2, 2018 at 12:16 pm
I'd do it with bitwise arithmetic:
Select t.Id, t.bit1, t.bit2, t.bit3, t.bit4, t.bit5, t.bit6, t.bit7, t.bit8If it was a large enough table and a query that runs a lot, I'd make that a calculated column so that I could index it directly.
August 2, 2018 at 8:07 pm
Don't forget that, unless there's a NOT NULL constraint on these columns, a NULL could end up being the fly in the ointment.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2018 at 10:20 pm
Here is one possible solution using a persisted calculated column (as already mentioned), with the addition of handling NULLs
😎
Can you post the DDL (create table) script for the table please, we are making the assumption that the datatype is a bit?
USE TEEST;
GO
SET NOCOUNT ON;
DECLARE @t table
(
Id int IDENTITY PRIMARY KEY
, bit1 bit
, bit2 bit
, bit3 bit
, bit4 bit
, bit5 bit
, bit6 bit
, bit7 bit
, bit8 bit
, BFLAG AS (ISNULL(SIGN(bit1),0) + ISNULL(SIGN(bit2),0) + ISNULL(SIGN(bit3),0) + ISNULL(SIGN(bit4),0)
+ ISNULL(SIGN(bit5),0) + ISNULL(SIGN(bit6),0) + ISNULL(SIGN(bit7),0) + ISNULL(SIGN(bit8),0)) PERSISTED
);
INSERT @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8)
VALUES (0, 1, 0, 0, 1, 0, 0, 0), (0, 0, 1, 0, 0, 1, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0), (1, 0, 0, 0, 1, 0, 0, 0)
, (0, 1, 0, 0, 1, 0, 0, 0), (0, 1, 1, 0, 0, 0, 0, 0), (1, 0, 0, 0, 0, 0, 1, 0), (1, 0, 0, 0, 1, 0, 0, 0)
, (1, 0, 0, 0, NULL, 0, 0, 0), (NULL, 0, 0, 0, NULL, 0, 0, 0);
SELECT
t.Id,t.bit1,t.bit2,t.bit3,t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
,t.BFLAG
FROM
@t t
WHERE T.BFLAG > 0;
August 3, 2018 at 8:38 am
judejay26 - Tuesday, June 19, 2018 6:34 AMI have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
Please post DDL and follow ANSI/ISO standards when asking for help.
August 3, 2018 at 8:44 am
jcelko212 32090 - Friday, August 3, 2018 8:38 AMjudejay26 - Tuesday, June 19, 2018 6:34 AMI have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to trueyour approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
While I agree that many people overuse the concept of flags, flags are an important part of data. Even MS uses them in their system tables and views. For example, IS_PrimaryKey and IS_Unique. Without those flags, there's a whole bunch of things you couldn't do in SQL Server
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2018 at 8:48 am
jcelko212 32090 - Friday, August 3, 2018 8:38 AMjudejay26 - Tuesday, June 19, 2018 6:34 AMI have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to trueyour approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
Rows are representations of records, so they are, in fact, like records to the point that the two terms are used interchangeably.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 3, 2018 at 9:49 am
jcelko212 32090 - Friday, August 3, 2018 8:38 AMjudejay26 - Tuesday, June 19, 2018 6:34 AMI have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to trueyour approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
Joe, can I ask you a favor, can you please post a solution that substantiate your post?
😎
August 3, 2018 at 10:55 am
Eirikur Eiriksson - Friday, August 3, 2018 9:49 AMjcelko212 32090 - Friday, August 3, 2018 8:38 AMjudejay26 - Tuesday, June 19, 2018 6:34 AMI have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to trueyour approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
Joe, can I ask you a favor, can you please post a solution that substantiate your post?
😎
He is correct about one thing... the OP needs to post the DDL for the table.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2018 at 2:46 pm
This was removed by the editor as SPAM
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply