How to use IF Condition in Where Clause in PLSQL?-CodePudding

SELECT (RC.TRR_BASE_AMT/INV_DTLS.TRD_ROE) FC_AMT  ,RC.*, HD.TRH_TRANS_REF ,BG.BKG_REF ,BG.BKG_BLNUM ,S.VOY_OP_SCH_VOY_REF AS TRH_VOYAGE_REF ,HD.TRH_TRANS_DATE  
    FROM  TAB_TDL_FIN_TRANSRECO RC
    LEFT JOIN TAB_THD_FIN_TRANSHEAD HD ON  TRR_MATCH_REF = HD.TRH_TRANS_REF  AND TRR_COMP_CODE = TRH_COMP_CODE      
    LEFT JOIN (SELECT TRR_SEQ AS INV_RECO_SEQ, TRD_ROE , ASSET_EMP_ID
                FROM TAB_TDL_FIN_TRANSDTLS ID, TAB_TDL_FIN_TRANSRECO IR
                WHERE TRD_COMP_CODE = TRR_COMP_CODE
                AND TRD_TRANS_REF = TRR_TRANS_REF
                AND TRD_SEQ = TRR_TDL_SEQ
              ) INV_DTLS ON   RC.TRR_MATCH_SEQ = INV_DTLS.INV_RECO_SEQ                         
    LEFT JOIN TAB_THD_BKGHEAD BG ON  BG.BKG_REF = INV_DTLS.ASSET_EMP_ID 
    LEFT JOIN TAB_MDL_VOY_OPER_SCHEDULE S ON S.VOY_OP_SCH_ID = CASE WHEN BG.BKG_SHPMT_TYPE ='O' THEN BG.BKG_POL_VOY_ID ELSE BG.BKG_POD_VOY_ID END      
    WHERE TRR_TRANS_TYPE ='CT' AND TRR_DRCR_FLAG  ='D' AND TRR_COMP_CODE = P_COMPCODE
    AND TRR_TRANS_REF >= NVL( P_CTVRFROM, TRR_TRANS_REF) AND  TRR_TRANS_REF <= NVL( P_CTVRTO, TRR_TRANS_REF)   
    AND (
        ( P_BLNOS IS NULL )
        OR
        ( BG.BKG_BLNUM IN (SELECT COLUMN_VALUE   FROM TABLE( IN_LIST_CLOB( REPLACE(REPLACE(REPLACE(P_BLNOS,CHR(9),''),CHR(10),''),' ',''))) WHERE COLUMN_VALUE IS NOT NULL)    )    
       )
    AND NVL(S.VOY_OP_SCH_VOY_REF,'X') = NVL( P_VOYCODE , NVL(S.VOY_OP_SCH_VOY_REF,'X') )
    AND HD.TRH_TRANS_DATE >= NVL(TO_DATE(P_FROMDATE,'DD/MM/YYYY') ,HD.TRH_TRANS_DATE )
    AND HD.TRH_TRANS_DATE <=  NVL(TO_DATE(P_TODATE,'DD/MM/YYYY') ,HD.TRH_TRANS_DATE )
    AND IF RC.TRR_TRANS_TYPE = 'IN' THEN >= NVL(TO_DATE(P_FROMDATE,'DD/MM/YYYY') ,RC.TRR_TRANS_DT ) END IF
    AND RC.TRR_MAC =P_ACNTCODE

CodePudding user response：

This is your IF:

AND IF RC.TRR_TRANS_TYPE = 'IN' THEN >= NVL(TO_DATE(P_FROMDATE,'DD/MM/YYYY') ,RC.TRR_TRANS_DT ) END IF

which reads as: if some value is IN, then something has to be >= than some date. What is that "something"? Is it HD.TRH_TRANS_DATE you're using right above that line? Let's presume it is; then - instead of IF (which is a PL/SQL condition) - use a CASE expression, e.g.

and HD.TRH_TRANS_DATE >= case when rc.trr_trans_type = 'IN' then 
                                NVL(TO_DATE(P_FROMDATE,'DD/MM/YYYY') ,RC.TRR_TRANS_DT )
                              else trunc(sysdate)
                         end

CodePudding user response：

Just use AND:

SELECT RC.TRR_BASE_AMT/INV_DTLS.TRD_ROE AS FC_AMT,
       RC.*,
       HD.TRH_TRANS_REF,
       BG.BKG_REF,
       BG.BKG_BLNUM,
       S.VOY_OP_SCH_VOY_REF AS TRH_VOYAGE_REF,
       HD.TRH_TRANS_DATE  
FROM   TAB_TDL_FIN_TRANSRECO RC
       LEFT JOIN TAB_THD_FIN_TRANSHEAD HD
       ON (   TRR_MATCH_REF = HD.TRH_TRANS_REF
          AND TRR_COMP_CODE = TRH_COMP_CODE )
       LEFT JOIN (
         SELECT TRR_SEQ AS INV_RECO_SEQ,
                TRD_ROE,
                ASSET_EMP_ID
         FROM   TAB_TDL_FIN_TRANSDTLS ID
                INNER JOIN TAB_TDL_FIN_TRANSRECO IR
                ON (   TRD_COMP_CODE = TRR_COMP_CODE
                   AND TRD_TRANS_REF = TRR_TRANS_REF
                   AND TRD_SEQ = TRR_TDL_SEQ
                )
       ) INV_DTLS
       ON RC.TRR_MATCH_SEQ = INV_DTLS.INV_RECO_SEQ                         
       LEFT JOIN TAB_THD_BKGHEAD BG
       ON  BG.BKG_REF = INV_DTLS.ASSET_EMP_ID 
       LEFT JOIN TAB_MDL_VOY_OPER_SCHEDULE S
       ON S.VOY_OP_SCH_ID = CASE
                            WHEN BG.BKG_SHPMT_TYPE ='O'
                            THEN BG.BKG_POL_VOY_ID
                            ELSE BG.BKG_POD_VOY_ID
                            END      
WHERE  TRR_TRANS_TYPE ='CT'
AND    TRR_DRCR_FLAG  ='D'
AND    TRR_COMP_CODE = P_COMPCODE
AND    (  TRR_TRANS_REF >= P_CTVRFROM
       OR (P_CTVRFROM IS NULL AND TRR_TRANS_REF IS NOT NULL))
AND    (  TRR_TRANS_REF <= P_CTVRTO
       OR (P_CTVRTO IS NULL AND TRR_TRANS_REF IS NOT NULL))
AND    (
          P_BLNOS IS NULL
       OR BG.BKG_BLNUM IN (
            SELECT COLUMN_VALUE
            FROM   TABLE(IN_LIST_CLOB(TRANSLATE(P_BLNOS,'X '||CHR(9)||CHR(10),'X')))
            WHERE  COLUMN_VALUE IS NOT NULL
          )
       )
AND    (  S.VOY_OP_SCH_VOY_REF = P_VOYCODE
       OR (P_VOYCODE IS NULL AND S.VOY_OP_SCH_VOY_REF IS NOT NULL))
AND    (  HD.TRH_TRANS_DATE >= TO_DATE(P_FROMDATE,'DD/MM/YYYY')
       OR (P_FROMDATE IS NULL AND HD.TRH_TRANS_DATE IS NOT NULL))
AND    (  HD.TRH_TRANS_DATE <=  TO_DATE(P_TODATE,'DD/MM/YYYY')
       OR (P_TODATE IS NULL AND HD.TRH_TRANS_DATE IS NOT NULL))
AND    RC.TRR_TRANS_TYPE = 'IN'
AND    something >= NVL(TO_DATE(P_FROMDATE,'DD/MM/YYYY') ,RC.TRR_TRANS_DT )
AND    RC.TRR_MAC =P_ACNTCODE