Bitand in the where clause

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 where clause, each bit does something different.

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

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

    
asked by anonymous 21.01.2015 / 14:25

1 answer

1

The solution looks like this:

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.
  );

Bitwise checks bit by bit whether one is connected or not, if it is connected, returns an integer greater than zero, when off it returns 0.

    
23.01.2015 / 14:27