Solved Is bitwise operation possible in queries?

[deletedT]

How can I use bitwise operation in a query?
I have this table:

Field 1	BitwiseProcess	Field2
prt1	52138​	AR12523AA431
prt2	50090​	AR12523AA432
prt3	49578​	AR12523AA433
prt4	49706​	AR12523AA434
prt5	49706​	AR12523AA435
prt6	50026​	AR12523AA436
prt7	17322​	AR12523AA437
prt8	35754​	AR12523AA438
prt9	2946​	AR12523AA439
prt10	2562​	AR12523AA440

When I write this query :

SELECT myTable.Field1
    ,[BitwiseProcess] AND 2048 AS Expr1
    ,myTable.BitwiseProcess
    ,myTable.Field2
FROM myTable
WHERE (myTable.Field1 LIKE "*prt*")
    AND ([BitwiseProcess]AND 2048) = 0;

the result is empty. I should have at least 4 or 5 records.


Any kind of advice is much appreciated.

 

[Deleted member 147267]

All I can add is the results of my curiosity which is in AWF already in case you don't get a definitive answer.

Can bitwise operators be employed in queries ?

For (a relatively simple) example; If I have a table tblBitMask with one field, BitMaskedField (number-byte data), that has 8 records:- tblBitMask.BitMaskedField 1 2 3 4 5 6 7 8 I want to run a query e.g. WHERE ((BitMaskedField AND 4) = true) I would expect to return:- 4,5,6 & 7 (but not 8...

www.access-programmers.co.uk

 

[deletedT]

I hoped I would find someway not to use a function. Because the table has almost half a million records and using external function makes it slow.

 

[Deleted member 147267]

Can you run this in the off hours but make it an append query that you dump into a table & have ready for start of business day?

 

[deletedT]

Never done that. Will try to see if it can cover the problem.
Thanks for the advice.

 

[Galaxiom]

Move the backend to SQL Server which does support bitwise operators.

 

[Deleted member 147267]

If that idea is new to you, it's often handled by Windows Task Scheduler. You might want to start researching that.
Where I worked, we had a pc & it's only purpose was to run db updates at night (there were many of them) so it was always left on. I used TS to launch a shortcut to open my db and run nightly updates. The reason for using a shortcut is that you can add a switch to it which gets passed to the db as a Command Property value. Thus in the startup code, if that value was present, I knew a pc was opening the db so all the usual user based code was not executed. I chose that route rather than maintaining 2 db's - one for users and one for updating at night.

 

[theDBguy]

Hi Tera. Just for fun, could you please try this?

SELECT myTable.Field1
,[BitwiseProcess]/2^11 Mod 2 AS Expr1
,myTable.BitwiseProcess
,myTable.Field2
FROM myTable
WHERE (myTable.Field1 LIKE "*prt*")
AND ([BitwiseProcess]/2^11 Mod 2) = 0;

Thanks!

 

[deletedT]

Hi Tera. Just for fun, could you please try this?
Thanks!

Sure. Just give me half an hour to go back to my desk.

by the way Thanks for trying.

 

[deletedT]

Hi Tera. Just for fun, could you please try this?

@theDBguy I think I've told you this before. But I really mean it this time. You're a genius. I never thought of a work around like this.
You can't even imagine how much you helped. I really appreciate it.

Million thanks

 

[theDBguy]

@theDBguy I think I've told you this before. But I really mean it this time. You're a genius. I never thought of a work around like this.
You can't even imagine how much you helped. I really appreciate it.

Million thanks

Hi. You're very welcome. Just glad to hear it worked for you. Good luck with your project.

 

[deletedT]

If that idea is new to you, it's often handled by Windows Task Scheduler. You might want to start researching that.
Where I worked, we had a pc & it's only purpose was to run db updates at night (there were many of them) so it was always left on. I used TS to launch a shortcut to open my db and run nightly updates. The reason for using a shortcut is that you can add a switch to it which gets passed to the db as a Command Property value. Thus in the startup code, if that value was present, I knew a pc was opening the db so all the usual user based code was not executed. I chose that route rather than maintaining 2 db's - one for users and one for updating at night.

I'm familiar with the process. My concern was what will happen if we receive an order in the morning and need to run the query immediately.

Thanks for suggesting anyway.

 

[Galaxiom]

My concern was what will happen if we receive an order in the morning and need to run the query immediately.

Can you run the function on receipt of each order and save the results?

 

[deletedT]

Can you run the function on receipt of each order and save the results?

Sorry for the late reply. We have a meeting this coming weekend with the users of this database. I will discuss your suggestion as well. For now @theDBguy 's workaround covers our problem. Thanks for sharing your experience.

 

[NauticalGent]

Bitwise?

 

[deletedT]

Bitwise?

Bitwise is a way to save a lot of boolean in a binary.
Bitwise operators are used to change individual bits in an operand. A single byte of computer memory when viewed as 8 bits-can signify the true/false status of 8 flags because each bit can be used as a boolean variable that can hold one of two values: true or false.

Using Bitwise Operators In VB

To demonstrate bitwise concepts, this article shows you how to store an array of Boolean flags within a single numeric variable.

www.jameshbyrd.com

 

[NauticalGent]

Thanks Tera, I'll give it a read. Domo arigato...