CODEWITHSUNDEEP
javasqlprepared-statementexecutequery
AllPower
AllPower

Reputation: 195

Pass Nullable values using Preparement state

I have this query, and I would like to pass the null value. How to do this using Preparement state

The column that must be null is E.RTCA_CD_QMNUM IN (:1)

String sqlSigiop = "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('"+dataInicio+"', 'DDMMYYYY') OR B.PLOP_DT_FECHAMENTO IS NULL)"
                + "AND (B.PLOP_DT_FECHAMENTO < TO_DATE('"+dataFim+"', 'DDMMYYYY') OR B.PLOP_DT_FECHAMENTO IS NULL) "
                + "AND A.MORE_SQ_MOTIVO_REPLANEJ IS NULL "
                + "AND B.PLOP_IN_STATUS = 2 "
                + "AND E.RTCA_CD_QMNUM IN (:1) "
                + "ORDER BY E.RTCA_CD_QMNUM, E.RTIT_NR_ITEM, D.RTSI_NR_SUBITEM, B.PLOP_DT_INICIO";

/

Statement stSigiop = cnnSigiop.createStatement();
    ResultSet resultSigiop = stSigiop.executeQuery(sqlSigiop.replace(nullable ":1", rts.toString().replace("[", "").replace("]", "")));
    while (resultSigiop.next()) {TO DO}

Upvotes: 0

Views: 43

Answers (1)

eshirvana
eshirvana

Reputation: 24568

you have to change your query to this :

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

Upvotes: 1

Related Questions