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.
| Author |
Topic |
|
R
Constraint Violating Yak Guru
328 Posts |
 Posted - 2011-11-03 : 07:24:59
|
We've hit a problem using the & operator in our code with large numbers. Basically these statements work well:SELECT * FROM tbl_users where (userPermissions & 32768 <> 0) SELECT * FROM tbl_users where (userPermissions & 1073741824 <> 0) When the number exceeds the maximum value of the Integer type, it fails. The column being used is actually BigIntSELECT * FROM tbl_users where (userPermissions & 2147483648 <> 0) The data types bigint and numeric are incompatible in the '&' operator.Could anyone please advise what workarounds (or better approaches) we can use here? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-03 : 07:26:09
|
| whats the datatype of userPermissions? try casting bigint value to same type------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-11-03 : 07:33:50
|
Hey visakh16Thanks for the fastest response in history...!userPermissions is BIGINT quote:
try casting bigint value to same type
Ahhhhhh... interesting, thisSELECT * FROM tbl_users where (userPermissions & CAST(2147483648 AS BIGINT) <> 0) works perfectly. Do you know why that is? Why would SQL not automatically use BigInt for this number? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-03 : 07:45:28
|
| i think it tries to cast it to numeric when using bitwise operator which is why you need explicit cast to bigint------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
R
Constraint Violating Yak Guru
328 Posts |
Posted - 2011-11-03 : 07:50:05
|
| Brilliant. Thank you for a very simple but effective solution :-) |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-03 : 07:54:29
|
| wc------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
The data types bigint and numeric are incompatible