Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    15

    Unanswered: DB2 Cursor implementation

    Hi,
    I need to modify a cursor but i am a bit confused how to do that. Need your expertise.

    Here is the CURSOR:-

    Code:
    SELECT
    VALUE(TABA.BILL_RSN_CODE,' '), DPASCD.IDN
    , DPASCD.PMT_DUE_DATE, DPASCD.BILL_DATE
    , DPASCD.BILL_MONTH, DPASCD.BILL_YEAR
    , TABA.REVENUE_MO, TABA.REVENUE_YR
    , TABA.PAYMENT_DUE_DATE, DPASCD.PMT_DUE_DLRS
    FROM (SELECT
    B.IDN AS DPAPLN_IDN,
    A.BILL_RSN_CODE,
    A.REVENUE_MO,
    A.REVENUE_YR,
    A.PAYMENT_DUE_DATE
    A.BILL_CYC_NUM 
    FROM CBIS.BILL_INFO_HIST A,
    CBIS.DEFERRED_PMT_PLAN B
    WHERE B.IDN = 2128597
    AND B.CUSACC_IDN = A.CUSACC_IDN
    AND A.CREATE_TMSTMP >= B.MONETARY_TMSTMP) AS TABA
    LEFT OUTER JOIN CBIS.DPA_PMT_SCHED DPASCD
    ON TABA.DPAPLN_IDN = DPASCD.DPAPLN_IDN
    AND INTEGER(TABA.REVENUE_MO) = DPASCD.BILL_MONTH
    AND INTEGER(TABA.REVENUE_YR) = DPASCD.BILL_YEAR
    ORDER BY DPASCD.PMT_DUE_DATE ASC
    WITH UR;
    ===================================
    here is what i want to implement in this cursor:---
    ===================================
    When BILL_CYC_NUM = 1
    AND DPASCD.BILL.DATE = 25 TO 31 (MONTH END DATES)

    PERFORM FOLLOWING:---

    WHEN REVENUE_MO = 1
    SET REVENUE_MO = 12
    AND REVENUE_YR = REVENUE_YR - 1

    WHEN REVENUE_MO <> 1
    THEN
    REVENUE_MO = REVENUE_MO - 1

    FOR OTHER THAN BILL_CYC_NUM 1 PERFORM THE CURSOR OPERTAION AS IT IS (just like before modification).

    Need your Urgent help.

    Thanks,
    Last edited by db2itg; 05-04-08 at 23:07.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at CASE expression. You can inject those in the SELECT list. It allows you to do the data modifications in the way you described, i.e:
    Code:
    SELECT CASE
              WHEN revenue_mo = 1
              THEN revenue_mo = 12
              ELSE revenue_mo = revenue_mo - 1
           END AS revenue_mo,
           CASE
              WHEN revenue_mo = 1
              THEN revenue_yr - 1
              ELSE revenue_yr
           END AS revenue_yr
    FROM ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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