Bitand in the Where clause

Asked

Viewed 91 times

1

I need to do the following Query, but it is generating some syntax or logic errors.

SELECT campo_X, campo_Y
FROM tabela_Z
WHERE( 
        ( 
           BITAND(1, 1) > 0 AND campo_A  NOT IN (2,4,8,9)
        )
        OR
        (
           BITAND(2, 2) > 0 
           AND campo_A NOT IN (2,4,8,9)
           AND campo_B = 1
        )
        -- Mais bitand's ...
      );

I will have to implement more blocks of bitand within the clause where, each bit does something different.

The error appears in the bitand > 0 when I run all select, but when I run only one of the block, that is, I comment on one and leave the other it executes and brings the information, but when I run the two blocks together it generates the error.

If anyone has another solution, I thought of a CASE or IF, but apparently the IF not acceptable within the clause Where.

pl-sql oracle10g

  • What is the reason for using BITAND ?!

  • This Select will be implemented in a SP that uses a Bit map, so the BITAND, the first value of the BITAND function will be informed by parameter of the INTEGER type. Then it may be that several bits are connected at the same time or only one of them, so for each 1 connected you should perform a condition for different data.

  • I do not know the background Function but from what I understood it returns the amount of conciliatory bits between two positive integers and , so I understood , if you want to compare bit by bit , I believe it would not be useful.

  • @Motta the question itself would not be the comparison, as the use of Function is quiet she meets, the question eh that I need it to work there within the Where clause. I’ve been able to do that, but there’s a problem. For example, the way the code snippet that I put here appears, Bit 0 and Bit 1 would be connected, so it would have to bring the result of the condition of bit 0, plus the result of the condition of bit 1. Motta you know some other logic for me to do this, currently use concatenation of Strings with Union, but my boss wants to optimize.

  • Tried to bring the binary value as string (I don’t know if you have Function for this) the comparison would be char to char from right to left.

  • Bitand works very well, problem in the Where clause, each bit goes a different Where the way this one works, only if two or more bits are connected it only shows the result of the first bit that it finds connected in the running stream, but I need it to run the others too if it is on, it can all be on, for example if I pass as argument the integer 255 to the first parameter of Function all bits would be on, then it would have to run all blocks inside the Where. I need a solution to resolve this issue. IF does not work.

Show 1 more comment

1 answer

1

The solution was thus:

SELECT z.campo_X, z.campo_Y, h.campo_H
FROM tabela_Z z
LEFT OUTER JOIN tabela_H h
ON z.campo_Z_COD = h.campo_H_COD
WHERE( 
      ( 
        BITAND(1, 1) > 0 
        AND z.campo_A  NOT IN (2,4,8,9)
      )
      OR
      (
        BITAND(2, 2) > 0 
        AND z.campo_A NOT IN (2,4,8,9)
        AND z.campo_B = 1
      )
      OR
      (
        BITAND(4, 4) > 0 
        AND z.campo_A NOT IN (2,4,8,9)
        AND z.campo_B = 2
        AND h.campo_H = 4258
      )
      -- aqui mais blocos até o bit 7, de 0 a 7, 8 Bits.
  );

Bitand checks bit by bit if one is on or not, if it has on returns interio greater than zero, when off returns 0.

Browser other questions tagged pl-sql oracle10g

You are not signed in. Login or sign up in order to post.