Handle Optional parameters using IS NULL with an IN clause-CodePudding

How would I do to receive null values or not, in the IN clause?

As I did below, when I put the null value, the search returns empty, but if I put it different from null, it returns.

My idea is to search only by date if the value of the IN clause is null

SELECT E.RTCA_CD_QMNUM
      ,E.RTIT_NR_ITEM
      ,D.RTSI_NR_SUBITEM
      ,B.PLOP_DT_INICIO
      ,B.PLOP_DT_FECHAMENTO
      ,D.RTSI_DT_MAIS_CEDO
      ,A.SSRT_CD_STATUS_SUBITEM_RT
      ,D.RTSI_DT_MAIS_TARDE
      ,A.PORT_DT_INCLUSAO
      ,C.PLCO_DS_PLANEJ_CONFIG
      ,F.ROTA_NM_ROTA
      ,H.CLLO_NM_CLUSTER_LOG
      ,G.ATEM_DT_INICIO
      ,I.TIAM_DS_TIPO_ATEND_MAR 
  FROM SIGIOP.PLANEJAMENTO_OPERACIONAL_RT A
 INNER JOIN SIGIOP.PLANEJAMENTO_OPERACIONAL B ON B.PLOP_SQ_PLANEJ_OPER = A.PLOP_SQ_PLANEJ_OPER
 INNER JOIN SIGIOP.PLANEJAMENTO_CONFIG C ON C.PLCO_SQ_PLANEJ_CONFIG = B.PLCO_SQ_PLANEJ_CONFIG
 INNER JOIN SIGIOP.RT_SUBITEM D ON D.RTSI_CD_RTSUBITEM = A.RTSI_CD_RTSUBITEM
 INNER JOIN SIGIOP.RT_ITEM E ON E.RTIT_CD_RTITEM = D.RTIT_CD_RTITEM
 INNER JOIN SIGIOP.ROTA F ON F.ROTA_SQ_ROTA = A.ROTA_SQ_ROTA
 INNER JOIN SIGIOP.ATENDIMENTO_MAR G ON G.ATEM_SQ_ATEND_MAR = A.ATEM_SQ_ATEND_MAR
 INNER JOIN SIGIOP.CLUSTER_LOG H ON H.CLLO_SQ_CLUSTER_LOG = G.CLLO_SQ_CLUSTER_LOG
 INNER JOIN SIGIOP.TIPO_ATENDIMENTO_MAR I ON I.TIAM_SQ_TIPO_ATEND_MAR = G.TIAM_SQ_TIPO_ATEND_MAR
 WHERE B.PLOP_DT_FECHAMENTO >= TO_DATE(:DATAINICIAL, 'DDMMYYYY') AND B.PLOP_DT_FECHAMENTO < TO_DATE(:DATAFINAL, 'DDMMYYYY')
   AND E.RTCA_CD_QMNUM IN (:1) OR E.RTCA_CD_QMNUM IS NULL
   AND A.MORE_SQ_MOTIVO_REPLANEJ IS NULL
   AND B.PLOP_IN_STATUS = 2
 ORDER BY E.RTCA_CD_QMNUM, E.RTIT_NR_ITEM, D.RTSI_NR_SUBITEM, B.PLOP_DT_INICIO;

CodePudding user response：

I think you only have to change this

AND ( E.RTCA_CD_QMNUM IN (:1) OR :1 IS NULL OR E.RTCA_CD_QMNUM IS NULL )

And for the dates, guessing you want to apply the same logic

WHERE 
( B.PLOP_DT_FECHAMENTO >= TO_DATE(:DATAINICIAL, 'DDMMYYYY') 
  OR 
  :DATINICIAL IS NULL OR B.PLOP_DT_FECHAMENTO IS NULL
) AND 
( B.PLOP_DT_FECHAMENTO < TO_DATE(:DATAFINAL, 'DDMMYYYY') 
  OR 
  :DATFINAL IS NULL OR B.PLOP_DT_FECHAMENTO IS NULL
)