Solved Is bitwise operation possible in queries? (1 Viewer)

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
How can I use bitwise operation in a query?
I have this table:
Field 1BitwiseProcessField2
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 :
SQL:
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.
 

Micron

AWF VIP
Local time
Today, 07:43
Joined
Oct 20, 2018
Messages
2,756
All I can add is the results of my curiosity which is in AWF already in case you don't get a definitive answer.
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
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.:cry:
 

Micron

AWF VIP
Local time
Today, 07:43
Joined
Oct 20, 2018
Messages
2,756
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?
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
Never done that. Will try to see if it can cover the problem.
Thanks for the advice.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 21:43
Joined
Jan 20, 2009
Messages
11,934
Move the backend to SQL Server which does support bitwise operators.
 

Micron

AWF VIP
Local time
Today, 07:43
Joined
Oct 20, 2018
Messages
2,756
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

I’m here to help
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
10,121
Hi Tera. Just for fun, could you please try this?
Code:
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!
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
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

I’m here to help
Local time
Today, 04:43
Joined
Oct 29, 2018
Messages
10,121
@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.
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
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

Super Moderator
Staff member
Local time
Today, 21:43
Joined
Jan 20, 2009
Messages
11,934
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?
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
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.
 

Tera

Registered User.
Local time
Today, 20:43
Joined
Feb 2, 2019
Messages
972
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.
 

NauticalGent

CopyPaster of the First Order
Local time
Today, 13:43
Joined
Apr 27, 2015
Messages
2,852
Thanks Tera, I'll give it a read. Domo arigato...
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom