T-SQL Bitwise OR not working - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL Bitwise OR not working

Author

Topic

DBouchard
Starting Member

1 Post

Posted - 2011-11-16 : 09:34:17

I’m under a big deadline for a customer, up most of the night, and I have a T-SQL script that aggregates the departments (from 0 to 49) a customer has shopped into a BIGINT value which is meant to be a mask of all the departments the customer has shopped. There’s a lot more going on, but I created this failing script and was hoping you could look at this quickly. Why does the final value in the table show 8 instead of 15???? I’m pulling my hair out!

DECLARE @Result TABLE (CustomerNum DECIMAL(10,0), Mask BIGINT)
DECLARE @Depts TABLE (CustomerNum DECIMAL(10,0), Dept INT)
DECLARE @Big2 BIGINT

SET NOCOUNT ON

SET @Big2 = 2

-- Initialize the row for this customer to be 0

INSERT @Result ( CustomerNum, Mask )
VALUES ( 12345, 0 )

INSERT @Depts (CustomerNum, Dept) VALUES (12345, 0)
INSERT @Depts (CustomerNum, Dept) VALUES (12345, 1)
INSERT @Depts (CustomerNum, Dept) VALUES (12345, 2)
INSERT @Depts (CustomerNum, Dept) VALUES (12345, 3)

SELECT * FROM @Result
SELECT * FROM @Depts

-- I was hoping that the following statement would loop through the rows in the @Depts table and OR the values there with the current mask value in the @Results table

UPDATE @Result SET Mask = R.Mask | POWER(@Big2, D.Dept) FROM @Result R INNER JOIN @Depts D ON R.CustomerNum = D.CustomerNum

SELECT * FROM @Result

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-11-16 : 10:07:57

[code]
INSERT INTO #Results
SELECT CustomerNum, SUM(POWER(2, Dept))
FROM #Depts
GROUP BY CustomerNum
[/code]

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-16 : 10:48:17

If you need to do an update, I took what Ifor did and combined it with your original query:

UPDATE 
    R 
SET 
    Mask = R.Mask | D.DeptMask
FROM 
    @Result R 
INNER JOIN 
    (
        SELECT CustomerNum, SUM(POWER(@Big2, Dept)) AS DeptMask
        FROM @Depts AS D
        GROUP BY CustomerNum    
    ) AS D 
    ON R.CustomerNum = D.CustomerNum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources