Detecting if a bit = 0 or 1 in a query (using MOD)

[MarkK]

And was there some constraint on calling a function from the query?

 

[gemma-the-husky]

Gemma -

SQL's "AND" operator is logical and there's no bitwise operator in SQL. It may work in VBA since VBA's "And" operator is bitwise and by extension could be also used as logical operation with caveats.

In SQL, though a "AND" would only give you "True" (aka -1) for the expression "128 AND 64" which isn't equal to 64 and therefore is false.

Furthermore, "8 AND 4" = -1 but do it in VBA and "8 And 4" = 0. Both are correct as long one is mindful of whether the And operator is a logical or bitwise operator and it's being used accordingly.

Banana

I am not quite following this - why would 8 AND 4 return true in a SQL AND. Surely 8 AND 4 is still false.

Anyway, I didn't realise you couldn't test a single bit - but surely you could still say

if field and 64 = true, to test whether a single bit is set.

I appreciate this amy not quite work out for multiple bits.

---------------------------

anyway, I researched further,and came across a reference to & as a bitwise operator in SQL

http://www.mssqltips.com/tip.asp?tip=1218

this article shows how a single byte can be used to store multiple y/n values.


Not sure if this is relevant though.

 

[Galaxiom]

MSSQL Server supports bitwise operators but Jet/ACE SQL doesn't.

 

[Galaxiom]

So the whole point of this thread was to try and nail down the mathematical equivalent to the bitwise expression.

Emulating fully fledged bitwise operations with Base10 arithmetic functions is certiainly not going to be performed with any manageable expression.

Essentially, arithmetic operations in SQL are functions designed to work with Base10 arguments. Base2 is completely foreign to them so any attempt to use arithmetic operators on binary must first deal with a base translation. Hence 2^n is evident in every expression we have discussed.

Moreover, bitwise is really just a way to represent multiple independent values in a single number. The whole value does not have a meaning as such. Likewise, bitwise operations are fully independent processes on each bit pair. Consequently there is no equivalent concept in any form of arithmetic that deals with the number as a whole.

The nearest decimal equivalent would be to use each digit to represent an independent value.

 

[Banana]

I am not quite following this - why would 8 AND 4 return true in a SQL AND. Surely 8 AND 4 is still false.

Because SQL's AND operator is a logical operator, not a bitwise operator. The equivalent in VBA would be more like: "CBool(x) And CBool". When you evaluate 8 and 4 both as a Boolean values, they turn into a True value and therefore it's "True And True", giving you a True.

anyway, I researched further,and came across a reference to & as a bitwise operator in SQL

http://www.mssqltips.com/tip.asp?tip=1218

Galaxiom is correct but just in case - whenever I spoke of "SQL", I didn't mean SQL Server and its dialect, Transact-SQL, but rather standard ANSI SQL, which is applicable to all SQL database products (at least as far as where they follow the standard).

 

[the_net_2.0]

And was there some constraint on calling a function from the query?

Yes, there is a constraint. Doing it would be similar (in processing time) to the way we are doing it now, which is looping recordsets and calling a one-line function to determine if the record should be updated.

Emulating fully fledged bitwise operations with Base10 arithmetic functions is certiainly not going to be performed with any manageable expression.

Essentially, arithmetic operations in SQL are functions designed to work with Base10 arguments. Base2 is completely foreign to them so any attempt to use arithmetic operators on binary must first deal with a base translation. Hence 2^n is evident in every expression we have discussed.

Moreover, bitwise is really just a way to represent multiple independent values in a single number. The whole value does not have a meaning as such. Likewise, bitwise operations are fully independent processes on each bit pair. Consequently there is no equivalent concept in any form of arithmetic that deals with the number as a whole.

The nearest decimal equivalent would be to use each digit to represent an independent value.

I do understand base systems. But the reasoning for this thread is still legit. =) It may require further testing on my part to get to a mathematical equivalent to NOT bitwise in a query, but lagbolt's expression may just give me a hint!

 

[gemma-the-husky]

Because SQL's AND operator is a logical operator, not a bitwise operator. The equivalent in VBA would be more like: "CBool(x) And CBool". When you evaluate 8 and 4 both as a Boolean values, they turn into a True value and therefore it's "True And True", giving you a True.



Galaxiom is correct but just in case - whenever I spoke of "SQL", I didn't mean SQL Server and its dialect, Transact-SQL, but rather standard ANSI SQL, which is applicable to all SQL database products (at least as far as where they follow the standard).

i see, said the blind man

in that case, i presume you would have to evaluate every row in the table to achieve this anyway - so is there any real benefit trying to do this all within SQL, compared with writing a VBA function to test the bit pattern - in which case you can use bitwise operations?

 

[Banana]

in that case, i presume you would have to evaluate every row in the table to achieve this anyway - so is there any real benefit trying to do this all within SQL, compared with writing a VBA function to test the bit pattern - in which case you can use bitwise operations?

Ding dong, we have a winner!

The expression "(Table.Field Mod x \ y) = 1" is definitely not sargable and therefore looping would be just as fast.

Come to think of it, even if we have & operator available to us, it'd still not be sargable unless we also create functional indexes.

I'd think that creating as many Yes/No fields, each being a bit and do get packed would be a better strategy; you can at least assign a power of 2 to each Yes/No if you wanted to consolidate them into a bitmask but still use SQL & indexes to get sargable query filtering out on certain bits.

 

[MarkK]

Sargable?

Sar'-ga-ble

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.
- From Wikipedia, the free encyclopedia

I love learning...

 

[the_net_2.0]

Re: Sargable?

Sar'-ga-ble

I love learning...

Does this mean that I can't count on any more help from you, SARGE?

 

[MarkK]

Does this mean that I can't count on any more help from you, SARGE?

Huh? I don't get the connection.

 

[ChrisO]

Public Declare Function timeGetTime Lib "Winmm.dll" () As Long


Public Sub Test()
    Dim lngI     As Long
    Dim lngStart As Long
    Dim lngLoop  As Long
    Dim X        As Long
    
    Const conLoopCount As Long = 1000000
    
    X = 129
    lngStart = timeGetTime()

    For lngI = 1 To conLoopCount
        [color=green]' Time the loop by itself.[/color]
    Next lngI

    lngLoop = timeGetTime() - lngStart          [color=green]'<< 15 milliseconds total.[/color]
    lngStart = timeGetTime()
    
    For lngI = 1 To conLoopCount
        [color=green]' Time the loop with the Function call.[/color]
        TestBitN X, 7
    Next lngI

    [color=green]' Function Call time for 1,000,000 Calls.[/color]
    MsgBox timeGetTime() - lngStart - lngLoop   [color=green]'<< 265 milliseconds total.[/color]

End Sub


Public Function TestBitN(ByVal lngValue As Long, _
                         ByVal bytBit As Byte) As Integer

    [color=green]' Valid Bit numbers are 0 to 31.[/color]
    If bytBit > 31 Then
        TestBitN = False
    Else
        If bytBit = 31 Then
            If lngValue < 0 Then TestBitN = True
        Else
            TestBitN = CBool(lngValue And (2 ^ bytBit))
        End If
    End If
    
End Function

Is it really worth trying to keep it in the query?