SQL & PL/SQL

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:

scott@ora102> select bitand(2, 2) from dual -- In binary: bitand(10, 10);

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

scott@ora102> select bitand(3, 2) from dual -- In binary: bitand(11, 10);

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

scott@ora102> select bitand(1, 2) from dual -- In binary: bitand(01, 10);

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

Daniel

On Jonathan Lewis' site you'll find a very nice review of bitand/bitor/etc.

http://www.jlcomp.demon.co.uk/faq/bitwise.html

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

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

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.

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

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

Of you need such a logic, you'd have to use (or build) a function that allows to split a number into primes.
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.

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

Not yet.
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.

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;

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

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.

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

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.