SQL and binary numbers

Question

SQL and binary numbers

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

siva 20997 (3/9/2012)
These are like Bits in a Byte being switched on or off
AND ing with whatever (in this case 32) is the correct way
However doing that seems to be a problem here
However you are lucky here because any number greater than 31 is is unconfirmed because it is the higest number
This will work as long as you do not have a 64 meaning They have gone bankrupt

I believe you're confusing the BIT number with the value to find it. An INT contains 4 bytes and has bits 0 through 31. Each BIT can be ANDed using the power of 2 for that bit. 2^31 is 2,147,483,648.

If you were constrained to a single byte (bits 0 through 7), the largest unsigned single bit value would be 128, not 64.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

siva 20997 Ten Centuries Points: 1181 More actions · Answer 2

This is what he said

Papersdue 1 ; Papers due (2^0)

Dueflag 2 ; Due letter sent flag (2^1)

Chaserflag 4 ; Chaser letter sent flag (2^2)

Feesettle 8 ; Fees settlement flag (2^3)

Statement 16 ; Statement flag (2^4)

Unconfirmed 32 ; Unconfirmed flag (2^5)

This is what I understand by it

Depending on the Status a differnt number will be stored in a single field

so fo example

If Papersdue it will store 1

if Due letter has been sent then it will store 2

however if Paersdue and DueFlagHas been sent it will store 1+2=3

So each flag has a value

if all flags has to be set ie Papersdue,Due letter has been sent etc etc the number stored will be

1+2+4+8+16+32 = 63

so without the unconfirmed flag being set the rest of the numbers can not get above 31

So I said if it is more than 31 the unconfirmed flag is set

Now I know how to do it if it had 64 and 128 too but I wont go into that now

People do this at byte level and it is known as masking or filtering as the CPU has a instruction set to do this I belive. Here someone has tried to do that with Integer numbers

I have done this long time back (in dbf) when I wanted to match House buyers to houses

depending on which boxes the purchaser ticks you work out a number

you do the same with house beased on ots properties

only the numbers are stored against the house and the buyer

by going through the algoritham you can work out which houses match the purchaser

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

The OP also said he was using DECIMAL. I understand the bit values being used but I didn't want people to mistake what you said for being limited to a max value of 32 for the largest bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

siva 20997 Ten Centuries Points: 1181 More actions · Answer 4

The decimal or integer doesnt matter as long as the principal is understood

This is what he said and can be represented further (just concentrate on the number not the decimal as the decimal part is not being used.) I have extended the number to binary notaion

Papersdue 1 ; Papers due (2^0) 00000001

Dueflag 2 ; Due letter sent flag (2^1) 00000010

Chaserflag 4 ; Chaser letter sent flag (2^2) 00000100

Feesettle 8 ; Fees settlement flag (2^3) 00001000

Statement 16 ; Statement flag (2^4) 00010000

Unconfirmed 32 ; Unconfirmed flag (2^5) 00100000

Bankrupt 64; Bankrupt Flag (2^6) 01000000

TakenOver 128; Taken Over (2^7) 10000000

So witht one byte (if it was interger) or more bytes if it was real,float we have represented 8 flags. This is how it is done when the memeorey is limited. You would see that in the computer BIOS parameters are set like that. not one byte per flag but one bit per flag

In this case the user has wasted space by putting it into a decimal field it should have been interger field

anyway if the unconfirmaedrmed flag is set the flags would look like this ??1????? where ? indicates that it can be 0 or 1

So AND ing it with the 32 like ??1????? and 00100000 would give result like

00000000 Unconfirmed flag is NOT set

00100000 Uncofirmaed flag is set

The fisrt answer by the Vetran was (Colname and 32)=32 was that.it should return 0 or 1. In other languages it does. the AND opertaor does that in Assembler , C , Pascal or any programming language

but not in TSQL and I am not sure of the reasons

So in the absence of AND operator how do you determine the 8 flags

1) divide the number by 128 first. if the result is 1 with a reminder then the 8th flag is set

2) divide the reminder with 64.if the result is 1 with a reminder then the 7th flag is set

3) divide the reminder with 32.if the result is 1 with a reminder then the 6th flag is set

That is what the AND Operator does

The technique they have used might sound archaic but that is how bit flags are set and we use one byte to set 8 flags. he dip switches on motherboars, network cards etc have these 8 switches, they are exavtley doing what I have said above to store one byte of information

Hope I have explained this 🙂

siva 20997 Ten Centuries Points: 1181 More actions · Answer 5

Sorry there is a & operator in SQL

and the solution given by the vetran does work once converted to int

siva 20997 Ten Centuries Points: 1181 More actions · Answer 6

It should read

Papersdue 1 ; Papers due (2^1) 00000001

Dueflag 2 ; Due letter sent flag (2^2) 00000010

Chaserflag 4 ; Chaser letter sent flag (2^3) 00000100

Feesettle 8 ; Fees settlement flag (2^4) 00001000

Statement 16 ; Statement flag (2^5) 00010000

Unconfirmed 32 ; Unconfirmed flag (2^6) 00100000

Bankrupt 64; Bankrupt Flag (2^7) 01000000

TakenOver 128; Taken Over (2^8) 10000000

I was hasty and made the mistake

Good that I didnt go into teaching proffesion

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

siva 20997 (3/10/2012)
That is what the AND Operator does

Just to be clear... AND does no form of division. You may be able to emulate it with division but I don't want people to walk away from this thread thinking that AND uses division in any way, shape or form because it doesn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

siva 20997 Ten Centuries Points: 1181 More actions · Answer 8

Agree AND is not divion (I didnt mean divion was AND, I meant the expalantion I had written is how AND works)

I was using the divion to convert our normal number to binary. The divion is the way humans have to do it. I am not sure of any other way

Similarly you have to use multiplication to convert a binary to a normal number