Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    3

    Red face Unanswered: need help with a query

    I am not a DBA, he has retired, we have this query that run for hours. can anyone give us a suggestion as to how to correct it?

    SELECT A.CLM_CASE_ID,A.WED,A.CLM_TIER_CD,WKLY_CERTN_TYP_C D,IND_PHY_ABLE,IND_AVLBT,IND_EMPLD,IND_WRK_SRCH_ST FY, IND_SCL_TRNG,CERTN_TS,A.EMPLR_ID,K.EAN, SEP_STAT_CD,GRS_WGE_ERND, IND_VAC_HDAY_PMT, VAC_PMT_AMT, HDAY_PMT_AMT, IND_STILL_IN_TRNG, A.IND_WWS, MODE_FLG_CD, IND_MLTRY_RSRVDUTY, TXT_WRK_NOT_STFY, G.PMT_MADE, H.PROGRAM_ID, H.MON_SEQ_NUM, C.UFACTS_CLMNT_ID, C.UFACTS_CLM_ID, B.CLMNT_ID, ESTBD_PENSN_AMT, ESTBD_PENSN_AMT_CD,EMPLR_CNTRBN, H.WBA, H.BEN_BAL, CASE WHEN EXISTS ( SELECT 1 FROM DB2INST1.TCLM_OVPT Z INNER JOIN DB2INST1.TCLM_TM_PNLTY T ON Z.OVPT_ID = T.OVPT_ID AND A.WED BETWEEN T.TM_PNLTY_START_DT AND T.TM_PNLTY_END_dT AND Z.OVPT_DETRM_CD = 1 INNER JOIN DB2INST1.TCLM_CLM X ON Z.CLM_CASE_ID = X.CLM_CASE_ID AND X.CLMNT_ID = B.CLMNT_ID) THEN 'Y' ELSE 'N' END AS WK_COMP_IN,(SELECT 'Y' FROM DB2INST1.TCLM_CLM_ISS ISS INNER JOIN DB2INST1.TCLM_ISS_DETRM DETRM ON ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID AND DETRM.DETRM_START_DT <= A.WED AND DETRM.DETRM_END_DT >= A.WED INNER JOIN DB2INST1.TCLM_DETRM_MAST DTRM_MAST ON DTRM_MAST.DETRM_ID = DETRM.DETRM_ID AND DTRM_MAST.ACTN_ID IN (2,3,4) WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID AND DETRM.DETRM_SEQ_NUM = (SELECT MAX(DETRM_SEQ_NUM) FROM DB2INST1.TCLM_ISS_DETRM Z WHERE Z.FCT_fNDG_ID = DETRM.FCT_FNDG_ID) UNION ALL SELECT 'Y' FROM DB2INST1.TCLM_CLM_ISS ISS INNER JOIN DB2INST1.TCLM_ISS_DETRM DETRM ON ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID AND DETRM.DETRM_START_DT <= A.WED AND DETRM.DETRM_END_DT >= A.WED INNER JOIN DB2INST1.TCLM_DETRM_MAST DTRM_MAST ON DTRM_MAST.DETRM_ID = DETRM.DETRM_ID INNER JOIN DB2INST1.TCLM_DERVD_DETRM DRVD ON DRVD.FCT_FNDG_ID = DETRM.FCT_FNDG_ID AND DRVD.DETRM_SEQ_NUM = DETRM.DETRM_SEQ_NUM AND DRVD.ACTN_ID IN (2,3,4) WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID FETCH FIRST 1 ROW ONLY) AS WEEK_DENIED_IN, (SELECT 'Y' FROM DB2INST1.TCLM_CLM_ISS ISS WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID AND ISS.ISS_STAT_CD = 1 AND ISS.ACTN_ID IN (2,3,4) FETCH FIRST 1 ROW ONLY) AS WEEK_HOLD_IN FROM DB2INST1.TCLM_WKLY_CERTN A INNER JOIN DB2INST1.TCLM_CLM B ON A.CLM_CASE_ID = B.CLM_CASE_ID INNER JOIN DB2INST1.CR_REF_CLM C ON B.CLMNT_ID = C.CLMNT_ID AND B.CLM_CASE_ID = C.CLM_CASE_ID INNER JOIN DB2INST1.CR_WED G ON A.CLM_CASE_ID = G.CLM_CASE_ID AND A.WED = G.WED AND G.PMT_MADE = 'N' INNER JOIN DB2INST1.CR_REF_MONE H ON G.MON_CRSS_REF_ID = H.MON_CRSS_REF_ID AND H.CLM_CASE_ID = B.CLM_CASE_ID LEFT OUTER JOIN (SELECT DISTINCT ESTBD_PENSN_AMT, ESTBD_PENSN_AMT_CD, EMPLR_CNTRBN, J.CLM_CASE_ID, L.DETRM_START_DT, L.DETRM_END_dT FROM DB2INST1.TCLM_PENSN_DETRM J INNER JOIN DB2INST1.TCLM_ISS_DETRM L ON J.FCT_FNDG_ID = L.FCT_FNDG_ID AND J.DETRM_SEQ_NUM = L.DETRM_SEQ_NUM AND J.PENSN_STAT_CD = 1) Z ON Z.CLM_CASE_ID = A.CLM_CASE_ID AND A.WED BETWEEN Z.DETRM_START_DT AND Z.DETRM_END_DT LEFT OUTER JOIN DB2INST1.TCLM_EMPLR K ON A.EMPLR_ID = K.EMPLR_ID WHERE B.CLM_CASE_ID BETWEEN ? AND ? ORDER BY A.CLM_CASE_ID, A.WED WITH UR

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    How about formatting it so it can be easily read? Also give us some background on the query. What size tables is it running against? What is the expected output? Have you run explain on it?

    Andy

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Also supply the DB2 version and OS.

  4. #4
    Join Date
    Oct 2005
    Posts
    3
    that one of the problems, it is written like it is displayed, 1 long line. i am not sure how to break it up

    running on using DB2 9.x

  5. #5
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    There is a 'floating' FY 8th select item.
    I inserted no spaces, other than to provide for what i thought
    should be indentation.

    Code:
    SELECT A.CLM_CASE_ID
          ,A.WED,A.CLM_TIER_CD
          ,WKLY_CERTN_TYP_C D
          ,IND_PHY_ABLE
          ,IND_AVLBT
          ,IND_EMPLD
          ,IND_WRK_SRCH_ST
           FY
          , IND_SCL_TRNG
          ,CERTN_TS
          ,A.EMPLR_ID
          ,K.EAN
          , SEP_STAT_CD
          ,GRS_WGE_ERND
          , IND_VAC_HDAY_PMT
          , VAC_PMT_AMT
          , HDAY_PMT_AMT
          , IND_STILL_IN_TRNG
          , A.IND_WWS
          , MODE_FLG_CD
          , IND_MLTRY_RSRVDUTY
          , TXT_WRK_NOT_STFY
          , G.PMT_MADE
          , H.PROGRAM_ID
          , H.MON_SEQ_NUM
          , C.UFACTS_CLMNT_ID
          , C.UFACTS_CLM_ID
          , B.CLMNT_ID
          , ESTBD_PENSN_AMT
          , ESTBD_PENSN_AMT_CD
          ,EMPLR_CNTRBN
          , H.WBA
          , H.BEN_BAL
          , CASE 
              WHEN EXISTS ( SELECT 1 
                              FROM DB2INST1.TCLM_OVPT Z 
                              INNER JOIN 
                                    DB2INST1.TCLM_TM_PNLTY T 
                                ON Z.OVPT_ID = T.OVPT_ID 
                               AND A.WED BETWEEN T.TM_PNLTY_START_DT AND T.TM_PNLTY_END_dT 
                               AND Z.OVPT_DETRM_CD = 1 
                                   INNER JOIN DB2INST1.TCLM_CLM X 
                                      ON Z.CLM_CASE_ID = X.CLM_CASE_ID 
                                     AND X.CLMNT_ID = B.CLMNT_ID) 
                     THEN 'Y' ELSE 'N' 
              END AS WK_COMP_IN
           ,(SELECT 'Y' 
              FROM DB2INST1.TCLM_CLM_ISS ISS 
              INNER JOIN DB2INST1.TCLM_ISS_DETRM DETRM 
                 ON ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID 
                AND DETRM.DETRM_START_DT <= A.WED 
                AND DETRM.DETRM_END_DT >= A.WED 
                    INNER JOIN 
                          DB2INST1.TCLM_DETRM_MAST DTRM_MAST 
                       ON DTRM_MAST.DETRM_ID = DETRM.DETRM_ID 
                      AND DTRM_MAST.ACTN_ID IN (2,3,4) 
              WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID 
                AND DETRM.DETRM_SEQ_NUM = (SELECT MAX(DETRM_SEQ_NUM) 
                                             FROM DB2INST1.TCLM_ISS_DETRM Z 
                                            WHERE Z.FCT_fNDG_ID = DETRM.FCT_FNDG_ID) 
              UNION ALL 
             SELECT 'Y' 
               FROM DB2INST1.TCLM_CLM_ISS ISS 
               INNER JOIN 
                     DB2INST1.TCLM_ISS_DETRM DETRM 
                  ON ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID 
                 AND DETRM.DETRM_START_DT <= A.WED 
                 AND DETRM.DETRM_END_DT >= A.WED 
                     INNER JOIN 
                           DB2INST1.TCLM_DETRM_MAST DTRM_MAST 
                        ON DTRM_MAST.DETRM_ID = DETRM.DETRM_ID 
                           INNER JOIN 
                                 DB2INST1.TCLM_DERVD_DETRM DRVD 
                              ON DRVD.FCT_FNDG_ID = DETRM.FCT_FNDG_ID 
                             AND DRVD.DETRM_SEQ_NUM = DETRM.DETRM_SEQ_NUM 
                             AND DRVD.ACTN_ID IN (2,3,4) 
             WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID 
             FETCH FIRST 1 ROW ONLY) AS WEEK_DENIED_IN
           , (SELECT 'Y' 
                FROM DB2INST1.TCLM_CLM_ISS ISS 
               WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID 
                 AND ISS.ISS_STAT_CD = 1 
                 AND ISS.ACTN_ID IN (2,3,4) 
               FETCH FIRST 1 ROW ONLY) AS WEEK_HOLD_IN 
    FROM DB2INST1.TCLM_WKLY_CERTN A 
         INNER JOIN 
               DB2INST1.TCLM_CLM B 
            ON A.CLM_CASE_ID = B.CLM_CASE_ID 
               INNER JOIN 
                     DB2INST1.CR_REF_CLM C 
                  ON B.CLMNT_ID = C.CLMNT_ID 
                 AND B.CLM_CASE_ID = C.CLM_CASE_ID 
                     INNER JOIN 
                           DB2INST1.CR_WED G 
                        ON A.CLM_CASE_ID = G.CLM_CASE_ID 
                       AND A.WED = G.WED 
                       AND G.PMT_MADE = 'N' 
                           INNER JOIN 
                                 DB2INST1.CR_REF_MONE H 
                              ON G.MON_CRSS_REF_ID = H.MON_CRSS_REF_ID 
                             AND H.CLM_CASE_ID = B.CLM_CASE_ID 
                                 LEFT OUTER JOIN 
                                            (SELECT DISTINCT ESTBD_PENSN_AMT
                                                  , ESTBD_PENSN_AMT_CD
                                                  , EMPLR_CNTRBN
                                                  , J.CLM_CASE_ID
                                                  , L.DETRM_START_DT
                                                  , L.DETRM_END_dT 
                                             FROM DB2INST1.TCLM_PENSN_DETRM J 
                                                  INNER JOIN 
                                                        DB2INST1.TCLM_ISS_DETRM L 
                                                     ON J.FCT_FNDG_ID = L.FCT_FNDG_ID 
                                                    AND J.DETRM_SEQ_NUM = L.DETRM_SEQ_NUM 
                                                    AND J.PENSN_STAT_CD = 1) Z 
                                   ON Z.CLM_CASE_ID = A.CLM_CASE_ID 
                                  AND A.WED BETWEEN Z.DETRM_START_DT AND Z.DETRM_END_DT 
                                      LEFT OUTER JOIN 
                                                 DB2INST1.TCLM_EMPLR K 
                                        ON A.EMPLR_ID = K.EMPLR_ID 
    WHERE B.CLM_CASE_ID BETWEEN ? AND ? 
    ORDER BY A.CLM_CASE_ID
           , A.WED 
    WITH UR
    Dick Brenholtz, Ami in Deutschland

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    1st plz ensure the statistics of the tables involved in the query is up to date。
    2nd plz run db2advis on the query to see whether there are some useful indexes recommended by optimizer。
    After that , if performace is not improved, plz put the full access plan ( the output of db2exfmt )
    and all tables ddl here。
    thx

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Which release/level of db2 is being used on which operating environment?

  8. #8
    Join Date
    Oct 2005
    Posts
    3
    Aix 5.3 .8 db2 udb 9.5

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I formatted your query by my way.
    (Breaking lines similar to Dick Brenholtz. But, some indentions are different.)

    Code:
    SELECT A.CLM_CASE_ID
         , A.WED
         , A.CLM_TIER_CD
         , WKLY_CERTN_TYP_CD
         , IND_PHY_ABLE
         , IND_AVLBT
         , IND_EMPLD
         , IND_WRK_SRCH_STFY
         , IND_SCL_TRNG,CERTN_TS
         , A.EMPLR_ID
         , K.EAN
         , SEP_STAT_CD
         , GRS_WGE_ERND
         , IND_VAC_HDAY_PMT
         , VAC_PMT_AMT
         , HDAY_PMT_AMT
         , IND_STILL_IN_TRNG
         , A.IND_WWS
         , MODE_FLG_CD
         , IND_MLTRY_RSRVDUTY
         , TXT_WRK_NOT_STFY
         , G.PMT_MADE
         , H.PROGRAM_ID
         , H.MON_SEQ_NUM
         , C.UFACTS_CLMNT_ID
         , C.UFACTS_CLM_ID
         , B.CLMNT_ID
         , ESTBD_PENSN_AMT
         , ESTBD_PENSN_AMT_CD
         , EMPLR_CNTRBN
         , H.WBA
         , H.BEN_BAL
         , CASE
           WHEN EXISTS
                ( SELECT 1
                   FROM  DB2INST1.TCLM_OVPT     Z
                   INNER JOIN
                         DB2INST1.TCLM_TM_PNLTY T
                    ON   Z.OVPT_ID = T.OVPT_ID
                     AND A.WED BETWEEN T.TM_PNLTY_START_DT
                                   AND T.TM_PNLTY_END_dT
                     AND Z.OVPT_DETRM_CD = 1
                   INNER JOIN
                         DB2INST1.TCLM_CLM      X
                    ON   Z.CLM_CASE_ID = X.CLM_CASE_ID
                     AND X.CLMNT_ID    = B.CLMNT_ID
                ) THEN
                'Y'
           ELSE 'N'
           END AS WK_COMP_IN
         , (SELECT 'Y'
             FROM  DB2INST1.TCLM_CLM_ISS   ISS
             INNER JOIN
                   DB2INST1.TCLM_ISS_DETRM DETRM
              ON   ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID
               AND DETRM.DETRM_START_DT <= A.WED
               AND DETRM.DETRM_END_DT   >= A.WED
            INNER JOIN
                  DB2INST1.TCLM_DETRM_MAST DTRM_MAST
             ON   DTRM_MAST.DETRM_ID = DETRM.DETRM_ID
              AND DTRM_MAST.ACTN_ID IN (2,3,4)
            WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID
              AND DETRM.DETRM_SEQ_NUM
                  = (SELECT MAX(DETRM_SEQ_NUM)
                      FROM DB2INST1.TCLM_ISS_DETRM Z
                      WHERE Z.FCT_fNDG_ID = DETRM.FCT_FNDG_ID
    /* Issue  1:
         Aren't these condiions necessary?
                        AND Z.DETRM_START_DT <= A.WED
                        AND Z.DETRM_END_DT   >= A.WED
    */
                    )
            UNION ALL
            SELECT 'Y'
             FROM  DB2INST1.TCLM_CLM_ISS   ISS
             INNER JOIN
                   DB2INST1.TCLM_ISS_DETRM DETRM
              ON   ISS.FCT_FNDG_ID = DETRM.FCT_FNDG_ID
               AND DETRM.DETRM_START_DT <= A.WED
               AND DETRM.DETRM_END_DT   >= A.WED
    /* Issue  2:
         Is this INNER JOIN necessary?
             INNER JOIN
                   DB2INST1.TCLM_DETRM_MAST DTRM_MAST
              ON   DTRM_MAST.DETRM_ID = DETRM.DETRM_ID
    */
             INNER JOIN
                   DB2INST1.TCLM_DERVD_DETRM DRVD
              ON   DRVD.FCT_FNDG_ID = DETRM.FCT_FNDG_ID
               AND DRVD.DETRM_SEQ_NUM = DETRM.DETRM_SEQ_NUM
               AND DRVD.ACTN_ID IN (2,3,4)
             WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID
             FETCH FIRST 1 ROW ONLY
           ) AS WEEK_DENIED_IN
         , (SELECT 'Y'
             FROM  DB2INST1.TCLM_CLM_ISS ISS
             WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID
               AND ISS.ISS_STAT_CD = 1
               AND ISS.ACTN_ID IN (2,3,4)
             FETCH FIRST 1 ROW ONLY
           ) AS WEEK_HOLD_IN
     FROM  DB2INST1.TCLM_WKLY_CERTN A
     INNER JOIN
           DB2INST1.TCLM_CLM        B
      ON   A.CLM_CASE_ID = B.CLM_CASE_ID
     INNER JOIN
           DB2INST1.CR_REF_CLM      C
      ON   B.CLMNT_ID = C.CLMNT_ID
       AND B.CLM_CASE_ID = C.CLM_CASE_ID
     INNER JOIN
           DB2INST1.CR_WED          G
      ON   A.CLM_CASE_ID = G.CLM_CASE_ID
       AND A.WED = G.WED
       AND G.PMT_MADE = 'N'
     INNER JOIN
           DB2INST1.CR_REF_MONE     H
      ON   G.MON_CRSS_REF_ID = H.MON_CRSS_REF_ID
       AND H.CLM_CASE_ID = B.CLM_CASE_ID
     LEFT  OUTER JOIN
    /* Issue  3:
         Is DISTINCT necessary?
    */
           (SELECT /* DISTINCT */
                   ESTBD_PENSN_AMT
                 , ESTBD_PENSN_AMT_CD
                 , EMPLR_CNTRBN
                 , J.CLM_CASE_ID
                 , L.DETRM_START_DT
                 , L.DETRM_END_dT
             FROM  DB2INST1.TCLM_PENSN_DETRM J
             INNER JOIN
                   DB2INST1.TCLM_ISS_DETRM   L
              ON   J.FCT_FNDG_ID   = L.FCT_FNDG_ID
               AND J.DETRM_SEQ_NUM = L.DETRM_SEQ_NUM
               AND J.PENSN_STAT_CD = 1
           )                        Z
      ON   Z.CLM_CASE_ID = A.CLM_CASE_ID
       AND A.WED BETWEEN Z.DETRM_START_DT
                     AND Z.DETRM_END_DT
     LEFT  OUTER JOIN
           DB2INST1.TCLM_EMPLR      K
      ON   A.EMPLR_ID = K.EMPLR_ID
     WHERE B.CLM_CASE_ID
           BETWEEN ? AND ?
     ORDER BY
           A.CLM_CASE_ID
         , A.WED
     WITH UR
    I had three isuues, and made opimistic(for improving/simplifying the query) assumptions, like...
    Code:
    ...
    /* Issue  1: Aren't these condiions necessary?
       Answer 1: Yes! these are necessary.
                        AND Z.DETRM_START_DT <= A.WED
                        AND Z.DETRM_END_DT   >= A.WED
    */
    ...
    /* Issue  2: Is this INNER JOIN necessary?
       Answer 2: No! these are not necessary.
             INNER JOIN
                   DB2INST1.TCLM_DETRM_MAST DTRM_MAST
              ON   DTRM_MAST.DETRM_ID = DETRM.DETRM_ID
    */
    ...
    /* Issue  3: Is DISTINCT necessary?
       Answer 3: No! these are not necessary.
    */
           (SELECT /* DISTINCT */
    ...
             FROM  DB2INST1.TCLM_PENSN_DETRM J
             INNER JOIN
                   DB2INST1.TCLM_ISS_DETRM   L
    ...
           )                        Z
    By these assumptions, I temporary reached this query.
    Would you please try the query and report the results?(error message(s), incorrect output or got succesful result?)
    Code:
    SELECT A.CLM_CASE_ID
         , A.WED
         , A.CLM_TIER_CD
         , WKLY_CERTN_TYP_CD    /* A. ? */
         , IND_PHY_ABLE
         , IND_AVLBT
         , IND_EMPLD
         , IND_WRK_SRCH_STFY
         , IND_SCL_TRNG,CERTN_TS
         , A.EMPLR_ID
         , K.EAN
         , SEP_STAT_CD
         , GRS_WGE_ERND
         , IND_VAC_HDAY_PMT
         , VAC_PMT_AMT
         , HDAY_PMT_AMT
         , IND_STILL_IN_TRNG
         , A.IND_WWS
         , MODE_FLG_CD
         , IND_MLTRY_RSRVDUTY
         , TXT_WRK_NOT_STFY
         , G.PMT_MADE
         , H.PROGRAM_ID
         , H.MON_SEQ_NUM
         , C.UFACTS_CLMNT_ID
         , C.UFACTS_CLM_ID
         , B.CLMNT_ID
         , J.ESTBD_PENSN_AMT
         , J.ESTBD_PENSN_AMT_CD
         , J.EMPLR_CNTRBN        /* L.EMPLR_CNTRBN ? */
         , H.WBA
         , H.BEN_BAL
         , CASE
           WHEN EXISTS
                (SELECT 1
                  FROM  DB2INST1.TCLM_CLM      X
                     /* DB2INST1.TCLM_CLM      B */
                  INNER JOIN
                        DB2INST1.TCLM_TM_PNLTY T
                  INNER JOIN
                        DB2INST1.TCLM_OVPT     Z
                   ON   Z.CLM_CASE_ID   = X.CLM_CASE_ID
                    AND Z.OVPT_ID       = T.OVPT_ID
                    AND Z.OVPT_DETRM_CD = 1
                  WHERE A.WED BETWEEN T.TM_PNLTY_START_DT
                                  AND T.TM_PNLTY_END_dT
                    AND X.CLMNT_ID = B.CLMNT_ID
                ) /* END-EXISTS */ THEN
                'Y'
           ELSE 'N'
           END AS WK_COMP_IN
         , CASE
           WHEN EXISTS
                (SELECT 0
                  FROM  DB2INST1.TCLM_CLM_ISS     ISS
                  INNER JOIN
                        LATERAL
                        (SELECT d.*
                              , ROW_NUMBER()
                                   OVER( ORDER BY DETRM_SEQ_NUM DESC ) AS row_num 
                          FROM  DB2INST1.TCLM_ISS_DETRM Z
                          WHERE Z.FCT_FNDG_ID = ISS.FCT_FNDG_ID
                            AND A.WED BETWEEN Z.DETRM_START_DT
                                          AND Z.DETRM_END_DT
                        )                         DETRM
                  LEFT  OUTER JOIN
                        DB2INST1.TCLM_DETRM_MAST  MAST
                   ON   DETRM.seq_num  =  1
                    AND MAST.DETRM_ID  =  DETRM.DETRM_ID
                    AND MAST.ACTN_ID   IN (2,3,4)
                  LEFT  OUTER JOIN
                        DB2INST1.TCLM_DERVD_DETRM DRVD
                   ON   MAST.ACTN_ID       IS NULL
                    AND DRVD.FCT_FNDG_ID   =  DETRM.FCT_FNDG_ID
                    AND DRVD.DETRM_SEQ_NUM =  DETRM.DETRM_SEQ_NUM
                    AND DRVD.ACTN_ID       IN (2,3,4)
                  WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID
                    AND
                   (    MAST.ACTN_ID IS NOT NULL
                    OR
                        DRVD.ACTN_ID IS NOT NULL
                   )
                ) /* END-EXISTS */ THEN
                'Y'
           ELSE 'N'
           END  AS WEEK_DENIED_IN
         , CASE
           WHEN EXISTS
                (SELECT 0
                  FROM  DB2INST1.TCLM_CLM_ISS ISS
                  WHERE ISS.CLM_CASE_ID = A.CLM_CASE_ID
                    AND ISS.ISS_STAT_CD = 1
                    AND ISS.ACTN_ID IN (2,3,4)
                ) /* END-EXISTS */ THEN
                'Y'
           ELSE 'N'
           END  AS WEEK_HOLD_IN
    
     FROM  DB2INST1.TCLM_WKLY_CERTN A
     INNER JOIN
           DB2INST1.TCLM_CLM        B
      ON   B.CLM_CASE_ID = A.CLM_CASE_ID
     INNER JOIN
           DB2INST1.CR_REF_CLM      C
      ON   C.CLM_CASE_ID = A.CLM_CASE_ID
       AND C.CLMNT_ID    = B.CLMNT_ID
     INNER JOIN
           DB2INST1.CR_WED          G
      ON   G.CLM_CASE_ID = A.CLM_CASE_ID
       AND G.WED         = A.WED
       AND G.PMT_MADE    = 'N'
     INNER JOIN
           DB2INST1.CR_REF_MONE     H
      ON   H.CLM_CASE_ID     = A.CLM_CASE_ID
       AND H.MON_CRSS_REF_ID = G.MON_CRSS_REF_ID
    
     LEFT  OUTER JOIN
           DB2INST1.TCLM_ISS_DETRM   L
     INNER JOIN
           DB2INST1.TCLM_PENSN_DETRM J
      ON   J.FCT_FNDG_ID   = L.FCT_FNDG_ID
       AND J.DETRM_SEQ_NUM = L.DETRM_SEQ_NUM
       AND J.PENSN_STAT_CD = 1
      ON   J.CLM_CASE_ID   = A.CLM_CASE_ID
       AND A.WED BETWEEN L.DETRM_START_DT
                     AND L.DETRM_END_DT
    
     LEFT  OUTER JOIN
           DB2INST1.TCLM_EMPLR      K
      ON   K.EMPLR_ID = A.EMPLR_ID
    
     WHERE B.CLM_CASE_ID BETWEEN ? AND ?
     ORDER BY
           A.CLM_CASE_ID
         , A.WED
     WITH UR
    ;
    Last edited by tonkuma; 08-01-12 at 20:56.

Posting Permissions

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