Queries using bitwise?

Hi Everyone,
I've using the query below to extract some information. ONe of the items in
the where clause is a check for an integer column for 0x1.
Question: What is the correct syntax for checking this column? As below, I
have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this?
Thanks in advance
Larry
SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
FROM
Account_Master am
INNER JOIN ClientProductCatalog cpc
ON am.AccountID = cpc.AccountID
INNER JOIN ProductMaster pm
ON cpc.ProductID = pm.ProductID
INNER JOIN ClientProductWorkflowStatus cpws
ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
INNER JOIN WorkflowStatusTypeLkp wfst
ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
INNER JOIN WorkflowTypeLkp wtl
ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
customer?
AND cpc.Deactivated = 0 -- Product still active?
AND pm.ProductStatus = 0x1 -- Awaiting status?> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
this?
The equal operator is NOT a bitwise operator. Therefore, the answer is "NO"
if you are attempting to limit the resultset to rows where ProductStatus has
the lowest bit set. If this is actually what you are after, then this
highlights a potential flaw in your design. Product status appears to
contain multiple pieces of information in a single column, a violation of
first normal form. On the flip side, perhaps you want status values that are
odd?
Assuming you want to continue down this path, then have a look in BOL for
the bitwise operators. As a hint, you will need to use the bitwise AND
operator, using an expression in the form of "(mycolumn AND y) = z" (note -
pseudocode).|||Larry,
How can we know if this is the correct syntax to check if you don't
say what you are trying to check? The query you posted will select
rows where pm.productStatus equals 1. If that's what you want, then
that's what you'll get. If that's not what you want, you need to tell us
what it is that you want. You say the query is returning rows. Have
you tried to run this on some test data to see if they are the rows you
want?
It's not clear why you are expressing the number 1 as 0x1, by the way.
The query you are posting here would be easier to read if you wrote
pm.productStatus = 1.
Please provide more information.
Larry wrote:

>Hi Everyone,
>I've using the query below to extract some information. ONe of the items i
n
>the where clause is a check for an integer column for 0x1.
>Question: What is the correct syntax for checking this column? As below,
I
>have pm.ProductStatus = 0x1. This is returning rows. Am I correct in this
?
>Thanks in advance
>Larry
>
>SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
>cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
>FROM
>Account_Master am
>INNER JOIN ClientProductCatalog cpc
>ON am.AccountID = cpc.AccountID
>INNER JOIN ProductMaster pm
>ON cpc.ProductID = pm.ProductID
>INNER JOIN ClientProductWorkflowStatus cpws
>ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
>INNER JOIN WorkflowStatusTypeLkp wfst
>ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
>INNER JOIN WorkflowTypeLkp wtl
>ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
>WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
>customer?
>AND cpc.Deactivated = 0 -- Product still active?
>AND pm.ProductStatus = 0x1 -- Awaiting status?
>|||Larry - The bitwise operators (from BOL) are
bitwise logical And ... &
bitwise logical or ... |
bitwise NOT ............ ~
bitwise exclusive or ... ^
So to test if an integral value has a particular bit SET
(Defined by a bitmask with all zeros but one)
Bitmask must be decimal 1,2,4,8,16, etc, or hex 0x1, 0x2, 0x4, 0x8, 0x10,
0x20, etc
use logical And ....... Where (Value & @.BitMask) <> 0
- If the result is non-zero, the Bit was set
To test if a particular bit is NOT SET
flip the bits of the value with NOT (~) and test that with the bitmask using
And (&)
Where (~Value & @.BitMask) <> 0
"Larry" wrote:

> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in thi
s?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||You can use a simple integer one constant for this. Why do you want to
make the code proprietary, unreadable and unmaintainable?
There are many things we do not do in SQL and one of them is low-level,
bit and byte operations. That is what assembly languages and things
like C are for. The next error is writing a lot of flags into the
schema, to mimic an old punch card file system. For example,
"deactivated" looks like a flag which should be a more general status
code or an even date.
We also do not construct data element names like "WorkflowTypeLkpID",
since a thing cannot be both a type code and an identfier. Where is
the "ClientProducts" or perhaps "ClientProductWorkflow" table which
would have "ClientProductWorkflowStatus" in a column, since a status
code is not an entity?
You confuse data and metadata concepts. I would bet you are used to an
OO language with lots of low-level constructs, without even a year of
data modeling.|||Is it not the case that bit wise operators are part of the SQL 99
specification?
Thomas
"Larry" <Larry@.discussions.microsoft.com> wrote in message
news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
> Hi Everyone,
> I've using the query below to extract some information. ONe of the items
> in
> the where clause is a check for an integer column for 0x1.
> Question: What is the correct syntax for checking this column? As below,
> I
> have pm.ProductStatus = 0x1. This is returning rows. Am I correct in
> this?
> Thanks in advance
> Larry
>
> SELECT am.AccountID, cpc.ProductID, cpc.ProductName, cpc.ManufacturerName,
> cpc.ManufacturerPartNumber, wtl.Description, cpws.Notes
> FROM
> Account_Master am
> INNER JOIN ClientProductCatalog cpc
> ON am.AccountID = cpc.AccountID
> INNER JOIN ProductMaster pm
> ON cpc.ProductID = pm.ProductID
> INNER JOIN ClientProductWorkflowStatus cpws
> ON cpc.ClientProductCatalogID = cpws.clientProductCatalogID
> INNER JOIN WorkflowStatusTypeLkp wfst
> ON cpws.WorkflowStatusTypeLkpID = wfst.WorkFlowStatusTypeLkpID
> INNER JOIN WorkflowTypeLkp wtl
> ON cpws.WorkflowTypeLkpID = wtl.WorkflowTypeLkpID
> WHERE wfst.WorkflowStatusTypeLkpID = 3 -- Needs more information from
> customer?
> AND cpc.Deactivated = 0 -- Product still active?
> AND pm.ProductStatus = 0x1 -- Awaiting status?|||Politics. There were products that still had a low-level
implementation based on particular hardware. Did you look at the
SQL:2003 that deprecared bit data types? Did you look at the problems
in implementations in actual products?|||Only AND, OR, and NOT, and only for data of type BOOLEAN, are
in the SQL-99 standard as far as I know. I wouldn't expect the standard
to specify bitwise operations for numerical data, or any other operators
that behaved as though the data were represented in a particular way.
Steve Kass
Drew University
Thomas C wrote:

>Is it not the case that bit wise operators are part of the SQL 99
>specification?
>
>Thomas
>"Larry" <Larry@.discussions.microsoft.com> wrote in message
>news:0F8877FE-B2B3-416C-9803-C2501F1C60A5@.microsoft.com...
>
>
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table[vbcol=seagreen]
>
>.
>|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>

QA returning wrong data

SQL 7.0
In QA:
"Select name from <table> where date = '20041010'
I get a list of 75,000 names. The name "Jones" is not in
the list.
However, if in QA:
"Select * from <table> where name = 'Jones' and ddate
= '20041010'
The name "Jones" is returned!
I can't figure this out for the life of me. It's a table
with 95 million records and a clustered index based on
name,date.
Any help appreciated.
thx,
Don
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
Just to make absolutely sure (nothing personal ), how are you
verifying that the name 'Jones' is not in the result set?
Assuming that Jones really isn't there, I would A) Make sure you're
upgraded to the latest service pack for SQL Server 7.0 (SP4, IIRC), B) Run
DBCC CHECKDB to verify that there's no data corruption, and C) Make sure you
look in the messages pane -- sometimes you'll get warnings indicating why
rows are not being returned.
|||Are these the exact queries you are running (copy and paste from QA)?
In one you are looking for date and in the other you are looking for ddate?
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
> SQL 7.0
> In QA:
> "Select name from <table> where date = '20041010'
> I get a list of 75,000 names. The name "Jones" is not in
> the list.
> However, if in QA:
> "Select * from <table> where name = 'Jones' and ddate
> = '20041010'
> The name "Jones" is returned!
> I can't figure this out for the life of me. It's a table
> with 95 million records and a clustered index based on
> name,date.
> Any help appreciated.
> thx,
> Don
>
|||Ok, here are the exact queries:
select name from maindata where ddate = '20041011'
select * from maindata where name = 'vpipx' and ddate
= '20041011'

>--Original Message--
>Are these the exact queries you are running (copy and
paste from QA)?
>In one you are looking for date and in the other you are
looking for ddate?
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in[vbcol=seagreen]
table
>
>.
>
|||In QA, I can do an Edit/Find, or I can copy the results
and paste into NotePad and search there, or I can scroll
down and see where the name should be alphabetically.
It's already running SP4.
Ran DBCC CHECKDB and no errors were returned.
There were no messages or messages pane.
Thx,
Don

>--Original Message--
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1ea801c4b52b$6ab9f710$a601280a@.phx.gbl...
in
> Just to make absolutely sure (nothing personal ),
how are you
>verifying that the name 'Jones' is not in the result set?
> Assuming that Jones really isn't there, I would A)
Make sure you're
>upgraded to the latest service pack for SQL Server 7.0
(SP4, IIRC), B) Run
>DBCC CHECKDB to verify that there's no data corruption,
and C) Make sure you
>look in the messages pane -- sometimes you'll get
warnings indicating why
>rows are not being returned.
>
>.
>
|||Can you post the CREATE TABLE DDL for maindata as well? Also please include
the results of SELECT @.@.VERSION.
Anith
|||>--Original Message--
>Can you post the CREATE TABLE DDL for maindata as well?
Also please include
>the results of SELECT @.@.VERSION.
>--
>Anith
>
Here you go..
Microsoft SQL Server 7.00 - 7.00.1094 (Intel X86) May 29
2003 15:21:25
Copyright (c) 1988-2002 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service
Pack 4)
(1 row(s) affected)
CREATE TABLE [dbo].[MAINDATA] (
[Name] [varchar] (32) NOT NULL ,
[DDate] [smalldatetime] NOT NULL ,
[DO] [decimal](18, 6) NOT NULL ,
[DH] [decimal](18, 6) NOT NULL ,
[DL] [decimal](18, 6) NOT NULL ,
[DC] [decimal](18, 6) NOT NULL ,
[DV] [int] NOT NULL ,
[DOI] [int] NOT NULL
) ON [PRIMARY]
GO
|||"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:1a9a01c4b536$557551f0$a401280a@.phx.gbl...
> Ok, here are the exact queries:
> select name from maindata where ddate = '20041011'
> select * from maindata where name = 'vpipx' and ddate
> = '20041011'
What happens when you try:
select name from maindata where name = 'vpipx' and ddate
= '20041011'
Also, can you check the execution plans of the two queries and see if
they're using different indexes? If so, can you try an index hint on the
first query to force use of the other index and see if that corrects the
issue?
|||> What happens when you try:
>select name from maindata where name = 'vpipx' and ddate
> = '20041011'
data returned: 'VPIPX'

> Also, can you check the execution plans of the two
queries and see if
>they're using different indexes? If so, can you try an
index hint on the
>first query to force use of the other index and see if
that corrects the
>issue?
looking at the execution plan for:
select name from maindata where ddate = '20041011'
Warning: Statistics missing for this table.
Choose 'Create Missing Statistics' from the context menu.
thx,
don

>.
>