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...
>
>
>
