Forum Stats

  • 3,733,249 Users
  • 2,246,737 Discussions
  • 7,856,634 Comments

Discussions

BITAND

483305
483305 Member Posts: 288
edited January 2007 in SQL & PL/SQL
can someone explain how the BITAND function works with an example? thanks

Comments

  • 18622
    18622 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
  • 245482
    245482 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
  • MichaelS
    MichaelS 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
  • 18622
    18622 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
  • cd_2
    cd_2 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.
  • MichaelS
    MichaelS 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
  • 18622
    18622 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
  • cd_2
    cd_2 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.
  • 18622
    18622 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
  • cd_2
    cd_2 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.
  • 245482
    245482 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;
  • 18622
    18622 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
  • MichaelS
    MichaelS 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.
  • 18622
    18622 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
  • cd_2
    cd_2 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.