Doubt referring to IN in Oracle

Question

Doubt referring to IN in Oracle

Navigation

#1 by (2 votes)

1

I'm setting up a query and I came up with an issue.

My query looks like this:

SELECT BASI_030.NIVEL_ESTRUTURA NIVEL,
       BASI_030.REFERENCIA GRUPO,
       BASI_030.DESCR_REFERENCIA,
       BASI_030.CONTA_ESTOQUE,
       BASI_020.TIPO_PRODUTO
  FROM BASI_030
  JOIN BASI_020
    ON BASI_030.NIVEL_ESTRUTURA = BASI_020.BASI030_NIVEL030
   AND BASI_030.REFERENCIA = BASI_020.BASI030_REFERENC
 WHERE BASI_030.NIVEL_ESTRUTURA = 2
   AND BASI_030.CONTA_ESTOQUE IN (77, 87, 92)
   AND BASI_030.REFERENCIA IN ( SELECT MCREF.REFERENCIA
                                  FROM MARK_CAMPANHA_REF MCREF
                                 WHERE MCREF.ID_CAMPANHA = 16 )

It is working, but when the table MARK_CAMPANHA_REF has no REFERENCE, I would have to bring all the references contained in the table BASI_030

As it stands, when the table MARK_CAMPANHA_REF does not have records for the CAM_ID, the query returns no value.

Could you help me?

oracle select where

asked by anonymous 07.07.2017 / 15:31

1 answer

Item in comboBox selected, display data in jTable How to get the context in a fragment?

score 2 · Accepted Answer

It would be a mix of LEFT JOIN with CTE to perform logic.

WITH sql AS (
    SELECT BASI_030.NIVEL_ESTRUTURA NIVEL,
           BASI_030.REFERENCIA GRUPO,
           BASI_030.DESCR_REFERENCIA,
           BASI_030.CONTA_ESTOQUE,
           BASI_020.TIPO_PRODUTO,
           MCREF.REFERENCIA REFERENCIA_MCREF   
    FROM BASI_030   
    JOIN BASI_020
       ON BASI_030.NIVEL_ESTRUTURA = BASI_020.BASI030_NIVEL030    
      AND BASI_030.REFERENCIA = BASI_020.BASI030_REFERENC  
    LEF JOIN MARK_CAMPANHA_REF MCREF
        ON MCREF.ID_CAMPANHA = 16
       AND MCREF.REFERENCIA = BASI_030.REFERENCIA
    WHERE BASI_030.NIVEL_ESTRUTURA = 2    
      AND BASI_030.CONTA_ESTOQUE IN (77, 87, 92) 
)
  , test AS (
    SELECT count(*) qtd
    FROM sql
    WHERE REFERENCIA_MCREF IS NOT NULL
)
SELECT *
FROM sql
WHERE  (
        CASE
        WHEN ((SELECT qtd
               FROM test) > 0)
          THEN
            REFERENCIA_MCREF IS NOT NULL
        ELSE
          1 = 1
        END)