Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2013
    Posts
    4

    Unanswered: Upgrade scope of existing union all...

    Using DB2 V9.7.500.702, Build Level S111017, PTF IP23287, Level IBM 1.6.0.

    Is it possible to tweek the SQL call below with its multiple 'union alls' so that the retrievals from the 'DFN_CSH_REC_TRN_SQ' table which are trying to 'exclude' building a result if an 'R03' TRAN_CD is found in that table......will look for that 'R03' TRAN_CD even if it is 'preceded' in the table by an 'R02' or other value which is lower in row sequence than 'R03'. The SQL fails to satisfy existing needs now because 'R02' or 'R01' may precede the 'R03's in that table and thus those rows are 'accepted' and retrieved.....but the 'R03' presence should prevent that from happening.

    EXEC SQL
    DECLARE READ-CURSOR CURSOR WITH HOLD FOR
    SELECT C.CSHRT_SEQ_NO,
    C.CSHRT_DT,
    DAYS(CURRENT_DATE) - DAYS(C.CSHRT_DT),
    C.FCRC_CD,
    C.CSHRT_CHK_AMT,
    C.CSHRT_UALCD_AMT,
    C.CSHRT_RNMG_BAL_AMT,
    A.CRAA_AMT,
    A.CRAA_DT AS TRAN_DATE,
    A.CRAN_CD AS ACTION_CODE,
    A.CAR_ID,
    C.RPTY_CD,
    C.BNK_ACC_ID
    FROM DFN_CASH_RECEIPT C,
    DFN_CRC_ALL_ACT A,
    DFN_CSH_REC_TRN_SQ T
    WHERE C.CSHRT_SEQ_NO = A.CSHRT_SEQ_NO
    AND C.CSHRT_SEQ_NO = T.CSHRT_SEQ_NO
    AND T.CRAN_CD <> 'R03'
    AND C.CSHRT_UALCD_AMT > 0
    AND C.CSHRT_RNMG_BAL_AMT > 0
    AND C.RSTY_CD > '2'
    AND C.CSHRT_SEQ_NO > 0
    UNION ALL

    SELECT C.CSHRT_SEQ_NO,
    C.CSHRT_DT,
    DAYS(CURRENT_DATE) - DAYS(C.CSHRT_DT),
    C.FCRC_CD,
    C.CSHRT_CHK_AMT,
    C.CSHRT_UALCD_AMT,
    C.CSHRT_RNMG_BAL_AMT,
    T.CTRA_AMT,
    T.CTRA_DT AS TRAN_DATE,
    T.CRAN_CD AS ACTION_CODE,
    T.CAR_ID,
    C.RPTY_CD,
    C.BNK_ACC_ID
    FROM DFN_CASH_RECEIPT C,
    DFN_CSH_REC_TRN_SQ T
    WHERE C.CSHRT_SEQ_NO = T.CSHRT_SEQ_NO
    AND C.CSHRT_UALCD_AMT > 0
    AND C.CSHRT_RNMG_BAL_AMT > 0
    AND C.RSTY_CD > '2'
    AND C.CSHRT_SEQ_NO > 0
    AND T.CRAN_CD <> 'R03'
    UNION ALL

    SELECT C.CSHRT_SEQ_NO,
    C.CSHRT_DT,
    DAYS(CURRENT_DATE) - DAYS(C.CSHRT_DT),
    C.FCRC_CD,
    C.CSHRT_CHK_AMT,
    C.CSHRT_UALCD_AMT,
    C.CSHRT_RNMG_BAL_AMT,
    L.CRCLA_APLD_AT,
    L.CRCLA_DT AS TRAN_DATE,
    'ADJ' AS ACTION_CODE,
    ' ' AS CAR_ID,
    C.RPTY_CD,
    C.BNK_ACC_ID
    FROM DFN_CASH_RECEIPT C,
    DFN_CASH_REC_ADJ A,
    DFN_CSH_REC_CLM_AJ L,
    DFN_CSH_REC_CLM_AJ L,
    DFN_CSH_REC_TRN_SQ T
    WHERE C.CSHRT_SEQ_NO = A.CSHRT_SEQ_NO
    AND C.CSHRT_SEQ_NO = T.CSHRT_SEQ_NO
    AND A.CRDAA_SEQ_NO = L.CRDAA_SEQ_NO
    AND C.CSHRT_UALCD_AMT > 0
    AND C.CSHRT_RNMG_BAL_AMT > 0
    AND C.RSTY_CD > '2'
    AND C.CSHRT_SEQ_NO > 0
    AND T.CRAN_CD <> 'R03'
    UNION ALL

    SELECT C.CSHRT_SEQ_NO,
    C.CSHRT_DT,
    DAYS(CURRENT_DATE) - DAYS(C.CSHRT_DT),
    C.FCRC_CD,
    C.CSHRT_CHK_AMT,
    C.CSHRT_UALCD_AMT,
    C.CSHRT_RNMG_BAL_AMT,
    CI.MLCR_CHK_AT,
    M.MLCR_SMIS_DT AS TRAN_DATE,
    'CRR' AS ACTION_CODE,
    ' ' AS CAR_ID,
    ' ' AS CAR_ID,
    C.RPTY_CD,
    C.BNK_ACC_ID
    FROM DFN_CASH_RECEIPT C,
    FIN_MLCR M,
    FIN_MLCRCI CI,
    DFN_CSH_REC_TRN_SQ T
    WHERE C.CSHRT_SEQ_NO = T.CSHRT_SEQ_NO
    AND C.CSHRT_SEQ_NO = M.CSHRT_SEQ_NO
    AND M.MLCR_SEQ_NO = CI.MLCR_SEQ_NO
    AND M.MLCRS_CD IN ('A', 'F', 'FA', 'N', 'P', 'S
    AND C.CSHRT_UALCD_AMT > 0
    AND C.CSHRT_RNMG_BAL_AMT > 0
    AND C.RSTY_CD > '2'
    AND C.CSHRT_SEQ_NO > 0
    AND T.CRAN_CD <> 'R03'
    ORDER BY FCRC_CD, CSHRT_DT, CSHRT_SEQ_NO,
    TRAN_DATE, ACTION_CODE
    FOR FETCH ONLY

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    do you mean you want to use a
    NOT IN ('R01','R02','R03')
    or
    > 'R03'
    or something else?

    Dave

  3. #3
    Join Date
    Jul 2013
    Posts
    4

    Upgrade scope of existing union all...

    The goal is to have the SQL spot 'any' occurrance of the 'R03' regardless of whether or not it is the 'first' entry found by the current fetch....currently only the first (lowest in sequence) row is returned.....hence 'R03's in subsequent rows are not noticed and a false positive for "<> 'R03" is resulting .......... since 'R01' or 'R02' which are not equal to 'R03'....satisy the SQL.....
    Somehow what's needed is to have the SQL 'see' the other row(s) one or more of which may be valued with 'R03' in TRAN_CD......and in so doing......NOT satisy the read (fetch) and NOT return a row to be processed.

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    John,
    Your description is still clear as mud here.

    Maybe you should try a NOT EXISTS subselect???

    and not exists (select 1 from DFN_CSH_REC_TRN_SQ T
    where C.CSHRT_SEQ_NO = T.CSHRT_SEQ_NO
    and AND T.CRAN_CD <> 'R03' )
    This would prevent you from seeing a row if there is a cran code with R03 in this table.

  5. #5
    Join Date
    Jul 2013
    Posts
    4
    Clear or not ....I like what you suggest.

  6. #6
    Join Date
    Jul 2013
    Posts
    4
    I did change the <> R03 in your alteration to = R03 as it seems to me that the "and NOT exists....." changed the nature of the request....and needed a reversal of the <> to = to get the desired result.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •