Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2012
    Posts
    31

    Unanswered: IBM DB2 - Need to get Max Date

    Hi-
    I've read through some of the other posts and have tried to reproduce the idea in my query but to no avail. I'm new to using subqueries (got the first one to work yesterday-- not this one though!)
    I have a table that houses several columns I wish to pull into a main query. The issue is that this table has multiple rows for each DRG code due to a different effective date, cancel date, and a change in description. When I've tried to pull in the MAX date for D_DRG_CANCEL, I of course lose DRG codes that don't have the overall table's MAX cancel date. Most codes have '9999-12-31', but one code that I miss has a cancel date of '2010-09-30'.

    Something like the below code can be a sub query as it pulls in the code with the correct cancel date. What I don't know is how to put this into the main query because of there being two fields involved:

    SELECT C_DRG, MAX(D_DRG_CANCEL)
    FROM DB2PROD.CRPTDRGCAT
    GROUP BY C_DRG

    Here is my latest failed attempt:
    SELECT C_DRG, T_DRG_SHORT
    FROM DB2PROD.CRPTDRGCAT
    WHERE C_DRG In (SELECT C_DRG, MAX(D_DRG_CANCEL)
    FROM DB2PROD.CRPTDRGCAT
    GROUP BY C_DRG)

    and the error message is
    Error during Execute
    428C4(-216)[IBM][CLI Driver][DB2] SQL0216N The number of elements on each side of a predicate operator does not match. Predicate operator is "IN". SQLSTATE=428C4

    which I understand but don't know how to resolve.

    Thanks for any assistance!

    Laura

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Lets see, you have one element on the left of the IN : C_DRG and two on the right: C_DRG, MAX(D_DRG_CANCEL). The number of elements needs to be the same.

    Andy

  3. #3
    Join Date
    Jul 2012
    Posts
    31
    and I'd pointed that out in my original post:
    Error during Execute
    428C4(-216)[IBM][CLI Driver][DB2] SQL0216N The number of elements on each side of a predicate operator does not match. Predicate operator is "IN". SQLSTATE=428C4

    which I understand but don't know how to resolve.


    I have the subquery working now anyway, now have to figure out how to work it into the main query.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Does this help or hinder?
    Code:
    SELECT C_DRG, T_DRG_SHORT 
       FROM DB2PROD.CRPTDRGCAT AS A
       WHERE C_DRG_CANCEL = (SELECT MAX(B.D_DRG_CANCEL) 
          FROM DB2PROD.CRPTDRGCAT AS B
          WHERE  B.C_DRG = A.C_DRG)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2012
    Posts
    31
    Thank you for your reply. A coworker was able to give me this working code:
    SELECT a.*
    FROM DB2PROD.CRPTDRGCAT a
    JOIN (SELECT C_DRG, max(D_DRG_CANCEL) AS MXDRGCNCL
    FROM DB2PROD.CRPTDRGCAT
    GROUP BY C_DRG) b on a.C_DRG = b.C_DRG AND A.D_DRG_CANCEL = B.MXDRGCNCL

    which pulls in just one row for each DRG with the max effective date.

    I tried to figure out how to apply this into my main query but can't, and the person I got the above code from is not available.

    I need to join the above result set to the C_DRG field in the table TMDTRPPRVSUM

    and what I'd REALLY like is to be able to pull in the field T_DRG_SHORT into the final result set.

    My attempt, which results in multiple rows (thus negating the subquery- ) --- so I know I haven't joined correctly. I need to get rid of the first instance of CRPTDRGCAT in the first FROM statement?? Is that actually what is represented by DB2PROD.CRPTDRGCAT a ??


    SELECT TMD.I_TRIMED_SYS_KEY, TMD.I_PROVIDER_NUMBER, TMD.C_PLAN,
    TMD.D_SERV_BEG, TMD.D_SERV_END, TMD.C_DRG,CRP.T_DRG_SHORT
    FROM DB2PROD.TMDTRPPRVSUM TMD,
    DB2PROD.CRPTDRGCAT CRP
    WHERE TMD.C_DRG=CRP.C_DRG
    AND TMD.D_SERV_BEG = '2012-09-17'
    AND TMD.C_PLAN = '423'
    AND TMD.C_DRG In (SELECT a.C_DRG
    FROM DB2PROD.CRPTDRGCAT a
    JOIN (SELECT C_DRG, max(D_DRG_CANCEL) AS MXDRGCNCL
    FROM DB2PROD.CRPTDRGCAT
    GROUP BY C_DRG) b on a.C_DRG = b.C_DRG AND A.D_DRG_CANCEL = B.MXDRGCNCL)

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Always format your code and use [CODE] tag, like ...
    Code:
    SELECT TMD.I_TRIMED_SYS_KEY
         , TMD.I_PROVIDER_NUMBER
         , TMD.C_PLAN
         , TMD.D_SERV_BEG
         , TMD.D_SERV_END
         , TMD.C_DRG,CRP.T_DRG_SHORT
     FROM  DB2PROD.TMDTRPPRVSUM TMD
         , DB2PROD.CRPTDRGCAT   CRP
     WHERE TMD.C_DRG      = CRP.C_DRG
       AND TMD.D_SERV_BEG = '2012-09-17'
       AND TMD.C_PLAN     = '423'
       AND TMD.C_DRG
           IN (SELECT a.C_DRG
                FROM  DB2PROD.CRPTDRGCAT a
                JOIN (SELECT C_DRG, max(D_DRG_CANCEL) AS MXDRGCNCL
                       FROM  DB2PROD.CRPTDRGCAT
                       GROUP BY
                             C_DRG
                     ) b
                 on   a.C_DRG        = b.C_DRG
                  AND A.D_DRG_CANCEL = B.MXDRGCNCL
              )
    If you could find a solution,
    it is not necessary to follow my advice.
    You can go your way.



    Try to replace "DB2PROD.CRPTDRGCAT CRP" in your code by a query which your coworker provided.
    Code:
    SELECT a.*
     FROM  DB2PROD.CRPTDRGCAT a
     JOIN (SELECT C_DRG
                , max(D_DRG_CANCEL) AS MXDRGCNCL
            FROM  DB2PROD.CRPTDRGCAT
            GROUP BY
                  C_DRG
          ) b
      on   a.C_DRG        = b.C_DRG
       AND A.D_DRG_CANCEL = B.MXDRGCNCL
    Like this ...
    Code:
    SELECT TMD.I_TRIMED_SYS_KEY
         , TMD.I_PROVIDER_NUMBER
         , TMD.C_PLAN
         , TMD.D_SERV_BEG
         , TMD.D_SERV_END
         , TMD.C_DRG
         , CRP.T_DRG_SHORT
     FROM  DB2PROD.TMDTRPPRVSUM TMD
         , /* DB2PROD.CRPTDRGCAT */
          (SELECT a.*
            FROM  DB2PROD.CRPTDRGCAT a
            JOIN (SELECT C_DRG
                       , max(D_DRG_CANCEL) AS MXDRGCNCL
                   FROM  DB2PROD.CRPTDRGCAT
                   GROUP BY
                         C_DRG
                 ) b
             on   a.C_DRG        = b.C_DRG
              AND A.D_DRG_CANCEL = B.MXDRGCNCL
          ) CRP
     WHERE TMD.C_DRG       = CRP.C_DRG
       AND TMD.D_SERV_BEG  = '2012-09-17'
       AND TMD.C_PLAN      = '423'
    /*
    By the way,
    is T_DRG_SHORT in your original code not necessary?

    */
    I made a misake in my formating at
    Code:
         , TMD.C_DRG,CRP.T_DRG_SHORT
    Afer I made proper formatting, I found that T_DRG_SHORT was used in final(outmost) SELECT.
    Code:
         , TMD.C_DRG
         , CRP.T_DRG_SHORT
    Last edited by tonkuma; 09-19-12 at 18:51. Reason: Make correction in formatting and replace final comment.

  7. #7
    Join Date
    Jul 2012
    Posts
    31

    Thumbs up

    THANK YOU VERY MUCH! I appreciate the time and thought you put into your response. It worked beautifully and helped me to understand.


    Laura

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just curious and have only read the exchange, not thought it out carefully. Wouldn't my suggestion above have produced the same results if you added the additional columns to the result set and criteria to the WHERE clause?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think that both of advice/example of Andy and PatP will work well same as a query provided by a coworker of Laura.

    Here I summarized possible alernatives(may be, not completely sure...).

    I want to recommend Example 6,
    and next to it Example 1 or 3 (and may be 4), because of simplicity, ease of understandings,
    and execuion efficiency(Example 6 references once the table, Example 1 or 3 using non-correlated subquery).
    NOT EXISTS predicate(like in Example 4) sometimes effecive to remove vague results (provided by unknown related to NULL values) and to clarify the meaning of the query.

    Example 1: Andy's advice.
    Code:
    SELECT C_DRG
         , T_DRG_SHORT
     FROM  DB2PROD.CRPTDRGCAT
     WHERE (C_DRG , D_DRG_CANCEL)
           IN
           (SELECT C_DRG , MAX(D_DRG_CANCEL)
              FROM  DB2PROD.CRPTDRGCAT
              GROUP BY
                    C_DRG
           )

    Example 2: PatP
    Code:
    SELECT C_DRG
         , T_DRG_SHORT
     FROM  DB2PROD.CRPTDRGCAT AS a
     WHERE D_DRG_CANCEL
         = (SELECT MAX(D_DRG_CANCEL)
             FROM  DB2PROD.CRPTDRGCAT AS b
             WHERE b.C_DRG = a.C_DRG
           )

    Example 3: Your coworker.
    Code:
    SELECT a.C_DRG
         , a.T_DRG_SHORT
     FROM  DB2PROD.CRPTDRGCAT a
     INNER JOIN
          (SELECT C_DRG
                , max(D_DRG_CANCEL) AS MXDRGCNCL
            FROM  DB2PROD.CRPTDRGCAT
            GROUP BY
                  C_DRG
          ) b
      ON   b.C_DRG     = a.C_DRG
       AND b.MXDRGCNCL = a.D_DRG_CANCEL

    Example 4:
    Code:
    SELECT C_DRG
         , T_DRG_SHORT
     FROM  DB2PROD.CRPTDRGCAT AS t
     WHERE NOT EXISTS
           (SELECT 0
             FROM  DB2PROD.CRPTDRGCAT AS s
             WHERE s.C_DRG = t.C_DRG
               AND s.D_DRG_CANCEL > t.D_DRG_CANCEL
           )

    Example 5:
    Code:
    SELECT a.C_DRG
         , a.T_DRG_SHORT
     FROM  DB2PROD.CRPTDRGCAT a
     INNER JOIN
           LATERAL
          (SELECT D_DRG_CANCEL
            FROM  DB2PROD.CRPTDRGCAT b
            WHERE b.C_DRG = a.C_DRG
            ORDER BY
                  D_DRG_CANCEL DESC
            FETCH FIRST 1 ROW ONLY
          ) b
      ON   b.D_DRG_CANCEL = a.D_DRG_CANCEL

    Other examples using OLAP specifications

    Example 6:
    Code:
    SELECT C_DRG
         , T_DRG_SHORT
     FROM (SELECT t.*
                , RANK()
                     OVER( PARTITION BY C_DRG
                               ORDER BY D_DRG_CANCEL DESC
                         ) AS rank_drg_cancel
            FROM  DB2PROD.CRPTDRGCAT AS t
          ) AS s
     WHERE rank_drg_cancel = 1
    or

    Example 7:
    Code:
    SELECT C_DRG
         , T_DRG_SHORT
     FROM (SELECT t.*
                , MAX(D_DRG_CANCEL)
                     OVER( PARTITION BY C_DRG
                         ) AS max_D_DRG_CANCEL
            FROM  DB2PROD.CRPTDRGCAT AS t
          ) AS s
     WHERE D_DRG_CANCEL = max_D_DRG_CANCEL
    Last edited by tonkuma; 09-20-12 at 15:31. Reason: Add LATERAL in Example 5.

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tonkuma View Post

    Here I summarized possible alernatives(may be, not completely sure...).
    I think this post by Tonkuma, and may be the entire thread, should belong to the sticky FAQ posting on this forum (that is, if we had one...)
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Jul 2012
    Posts
    31
    WOW! I had no idea people were still working through this, as I'd been able to use the code posted 09-19-12, 21:23 . I came back here to ask for some help on using this subselect as a left outer join. So,

    SELECT TMD.I_TRIMED_SYS_KEY
    , TMD.I_PROVIDER_NUMBER
    , TMD.C_PLAN
    , TMD.D_SERV_BEG
    , TMD.D_SERV_END
    , TMD.C_DRG
    , CRP.T_DRG_SHORT
    , CRP.D_DRG_CANCEL
    FROM DB2PROD.TMDTRPPRVSUM TMD
    , /* DB2PROD.CRPTDRGCAT *---how do I left join this??/
    LEFT OUTER JOIN (SELECT a.*
    FROM DB2PROD.CRPTDRGCAT a
    JOIN (SELECT C_DRG
    , max(D_DRG_CANCEL) AS MXDRGCNCL
    FROM DB2PROD.CRPTDRGCAT
    GROUP BY
    C_DRG
    ) b
    on a.C_DRG = b.C_DRG
    AND A.D_DRG_CANCEL = B.MXDRGCNCL
    ) CRP ON TMD.C_DRG=CRP.C_DRG

    WHERE TMD.D_SERV_BEG Between '2012-09-01' AND '2012-09-20'
    AND TMD.C_PLAN = '423'

    When I try this it isn't finding the CRP table. This is the error:
    Error during Execute
    S0002(-204)[IBM][CLI Driver][DB2] SQL0204N "TMDPROD.LEFT" is an undefined name. SQLSTATE=42704

    I need to use a left outer join because of additional conditions in my complete query.

    This forum is FABULOUS!
    Laura

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    you don't need a comma before the LEFT OUTER JOIN.

    P.S. It might be a good idea to start a new thread for a new question.
    Last edited by n_i; 09-24-12 at 14:19.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Jul 2012
    Posts
    31
    duh.
    Thank you

Posting Permissions

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