April 2, 2009 at 2:21 am
I have this simple problem:
I can perform following queries:
SELECT COUNT(1) FROM
WHERE [col] & POWER(-2,29) != 0
SELECT COUNT(1) FROM
WHERE [col] & POWER(2,30) != 0
SELECT POWER(-2,31) & 1
but I can't perform:
SELECT COUNT(1) FROM
WHERE [col] & POWER(-2,31) != 0
despite POWER(-2,31) being a correct integer value. The error says that numeric and integer are incompatible types in bit & operator. Gosh. Even the following:
SELECT COUNT(1) FROM
WHERE CAST([col] AS INT) & CAST(POWER(-2,31) AS INT) != CAST(0 AS INT)
does not work (which shouldn't really make any difference because POWER(-2,31) and [col] are integers).
The [col] definition is INT, NOT NULL
I understand that I can check for this bit with clasue:
...[col] < 0
but anyway this is really weird. Is there any workaround - or even better flaw in my code?
April 2, 2009 at 2:43 am
I guess it is the problem with values inside [col] column. Some how it is exceeding the INT size limit. Please make sure [col] is INT not BIGINT.
Regards,
Nitin
April 2, 2009 at 3:21 am
I checked it three times - it is just INT, not BIGINT. Besides i think BIGINT is actually compatible. Anyway - this is just ordinary INT column. And also - if the column exceeds INT limits other queries (with ... & power(2,30) for example) should fail in the same way, shouldn't they?
Anyway - I will say it once again - this is INT column.
And additionall - weird - thing.
I work with 2 (physically 2) computers with SQL Server 05 - let call them A and B.
I have linked computer B to A so that I can perform queries on tables on computer B working on connection to computer A. Then - the previosly mentioned query was performed on connection to computer A and properly addressing table on computer B.
If I connect directly to computer B the query is performed properly. Weird.
April 2, 2009 at 3:31 am
This works fine for me...
IF ( OBJECT_ID( 'tempdb..#SomeTable' ) IS NOT NULL )
DROP TABLE #SomeTable
CREATE TABLE #SomeTable
(
SomeColumn1 INT NOT NULL,
SomeColumn2 INT NOT NULL
)
INSERT #SomeTable( SomeColumn1, SomeColumn2 )
SELECT TOP 10 ( CHECKSUM( NEWID() ) ), ( CHECKSUM( NEWID() ) )
FROM sys.columns
SELECT COUNT( * )
FROM #SomeTable
WHERE SomeColumn2 & POWER( -2, 31 ) != 0
SELECT COUNT( * )
FROM #SomeTable
WHERE SomeColumn2 & POWER( -2, 29 ) != 0
--Ramesh
April 2, 2009 at 4:00 am
Try to link remote server and do it with
[remote_computer].[database].[schema].
instead of
[database].[schema].
April 2, 2009 at 4:07 am
nimdil (4/2/2009)
Try to link remote server and do it with[remote_computer].[database].[schema].
instead of
[database].[schema].
It still works fine...
EXECUTE ( 'SELECT COUNT( * ) FROM TestServer.RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 29 ) != 0' )
SELECT * FROM OPENQUERY( TestServer , 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 29 ) != 0' )
Edit:
1. Added OPENQUERY example
--Ramesh
April 2, 2009 at 4:15 am
As I stated previously - it works fine with POWER(-2,29); It crushes only with POWER(-2,31)
If it works fine with you - well. Something is unfortunately wrong with my configuration
April 2, 2009 at 4:27 am
nimdil (4/2/2009)
As I stated previously - it works fine with POWER(-2,29); It crushes only with POWER(-2,31)If it works fine with you - well. Something is unfortunately wrong with my configuration
Aaah, now I see, there's indeed a issue with EXECUTE (not linked server version)
-- This raises an error "The data types int and numeric are incompatible in the boolean AND operator"
EXECUTE ( 'SELECT COUNT( * ) FROM TestServer.RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' )
-- This executes without any error
EXECUTE ( 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' ) AT TestServer
-- This executes without any error
SELECT * FROM OPENQUERY( TestServer , 'SELECT COUNT( * ) FROM RnD.dbo.trnOrders WHERE OrderID & POWER( -2, 31 ) != 0' )
--Ramesh
April 2, 2009 at 7:42 am
Wow, thanks for the solution. It makes code look simpler.
Do you know why the first syntax returns error? Shouldn't they be equivalent or s.t.?
April 2, 2009 at 8:10 am
nimdil (4/2/2009)
Wow, thanks for the solution. It makes code look simpler.Do you know why the first syntax returns error? Shouldn't they be equivalent or s.t.?
I'm still trying find why it failed at first?:cool:;-) I'll ping back when I get the answer for it!!
--Ramesh
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply