## Forum Stats

• 3,733,249 Users
• 2,246,737 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# BITAND

Member Posts: 288
edited January 2007
can someone explain how the BITAND function works with an example? thanks

• Member Posts: 583
Just rewrite the arguments in binary, and match them position-by-position. Remember that in logic, it takes 2 "1's" to result in a "1" when "and'ed" together:

[email protected]> select bitand(2, 2) from dual -- In binary: bitand(10, 10);

BITAND(2,2)
-----------
2

[email protected]> select bitand(3, 2) from dual -- In binary: bitand(11, 10);

BITAND(3,2)
-----------
2

[email protected]> select bitand(1, 2) from dual -- In binary: bitand(01, 10);

BITAND(1,2)
-----------
0

Daniel
• Member Posts: 1,254
On Jonathan Lewis' site you'll find a very nice review of bitand/bitor/etc.

http://www.jlcomp.demon.co.uk/faq/bitwise.html
• Member Posts: 8,424 Bronze Crown
Obviously from your results one sees that there is no 1:1 correspondence between the input parameters and the ouput:

2,2 results in 2

as well as

3,2 results in 2

Is it somehow possible to provide a formula so that a 1:1 relation can be accomplished?
I would like to have a formula so that from the result I can deduce the 2 input parameters! Is this somehow possible with (a combination) from BITAND's?

regards
Michael
• Member Posts: 583
I won't be able to provide that, and I'm sure no one else will, since it's a property of that operand that given the result, multiple arguments are possible! I think most of the operands taking multiple arguments behave this way. For example, for the addition, 5 can be the sum of 1+4 and also 2+3.

Daniel
• Member Posts: 5,021
edited January 2007
Just to illustrate the problem, here's a small example:
```WITH t AS (SELECT 0 col1
FROM dual
UNION
SELECT 1 col1
FROM dual
)
SELECT t1.col1, t2.col1, bitand(t1.col1, t2.col1)
FROM t t1
, t t2
;       ```
Question to michaels: Is there any business case/logic behind your question?

C.
• Member Posts: 8,424 Bronze Crown
I know about the ambiguity in general if you have two input parameters resulting in just one number. But there are exceptions as well: Think about primes and multiplication: I always can reconstruct the multipliers given a prime number:

5 is always 1*5
7 is always 1*7
....

but primes are too sparse in my case.
My simplified business case is as follows:
I'll get a 3-digit number which I have to split in 2 numbers for insertion in two different tables:
eg. 775 => insert key1 into table 1; insert key2 into table 2 (Think e.g. of orders and orderlines respectivly)
When selecting again from the two tables i have to reconstruct 775 again from the two keys. One idea was to separate 775 in 77 and 5 ....but this restricts the first number being between 1 and 99 and the second number from just being between 1 and 9 which is out of discussion.

Michael
• Member Posts: 583
Why isn't it acceptable if the first number is between 1 and 99, and the second one from 1 to 9? That was just the suggestion I had in mind for you ("concatenate" 2 numbers). At least that preserves uniqueness of the arguments, given the result. You won't be able to get that using BITAND.

Daniel
• Member Posts: 5,021
edited January 2007
I know about the ambiguity in general if you have two
input parameters resulting in just one number. But
there are exceptions as well: Think about primes and
multiplication: I always can reconstruct the
multipliers given a prime number:

5 is always 1*5
7 is always 1*7
....
Of you need such a logic, you'd have to use (or build) a function that allows to split a number into primes.
but primes are too sparse in my case.
My simplified business case is as follows:
I'll get a 3-digit number which I have to split in 2
numbers for insertion in two different tables:
eg. 775 => insert key1 into table 1; insert key2
into table 2 (Think e.g. of orders and orderlines
respectivly)
When selecting again from the two tables i have to
reconstruct 775 again from the two keys. One idea was
to separate 775 in 77 and 5 ....but this restricts
the first number being between 1 and 99 and the
second number from just being between 1 and 9 which
is out of discussion.

Michael
I'm still curios why you have to split the number instead of inserting it into both tables, but you could try a simple "XOR":
```SELECT 1023 - 755
FROM dual;

=> 268

SELECT 1023 - 268
FROM dual;

=> 755```
C.
• Member Posts: 583
What do you mean by XOR? Does Oracle have such an operand? Even if it does, I' m sure it wouldn't preserve the uniqueness of the operands given a result (from what I remember of my math courses). Also, I don't understand your example. Given 268, the operands could have been 1023 - 755, 1022 - 754, ...

Daniel
• Member Posts: 5,021
What do you mean by XOR? Does Oracle have such an
operand?
Not yet.
Even if it does, I' m sure it wouldn't
preserve the uniqueness of the operands given a
result (from what I remember of my math courses).
Also, I don't understand your example. Given 268, the
operands could have been 1023 - 755, 1022 - 754, ...
You'd have to use a constant of course, if you have to generate a second number for your your initial value. I'm still curious why you have to generate a value pair instead of writing the key in both tables.

C.
• Member Posts: 1,254
From Jonathan Lewis' article (above)
```function bitxor(p_dec1 number, p_dec2 number) return number is
begin
return bitor(p_dec1,p_dec2)-bitand(p_dec1,p_dec2);
-- or you could use: return p_dec1-2*bitand(p_dec1,p_dec2)+p_dec2;
end;```
• Member Posts: 583
I've not read the article, but when I try to compile this function, Oracle doesn't know what BITOR is. I don't either. Oracle 10.2 on Win XP.

Daniel
• Member Posts: 8,424 Bronze Crown
It is also mentioned in J.Lewis article:

function bitor(p_dec1 number, p_dec2 number) return number is
begin
return p_dec1-bitand(p_dec1,p_dec2)+p_dec2;
end;

Still I don't understand cd's XOR and how it could resolve my problem. The ambiguity still prevails as far as I can see.
• Member Posts: 583
I also don't see how it would resolve your problem. Given a result for that operand, there is no way you'll be able to uniquely identify the arguments. You need a different operand to achieve this.

Daniel
• Member Posts: 5,021
edited January 2007
Still I don't understand cd's XOR and how it could
resolve my problem. The ambiguity still prevails as
far as I can see.
If you have at least one of the keys and the said constant, you can always find the other one, and there won't be any ambiguity. An XOR implementation without the necessary data won't help you either, but I still don't understand your business case or how you want to achieve joins between different tables without the necessary information.

C.
This discussion has been closed.