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

    Unanswered: Using select MAX in within Left Join

    Hello-

    I need to use this code to pull in one row per member:
    Code:
    AND      (OPR1.D_HCID_END_USE = (SELECT MAX(D_HCID_END_USE) FROM DB2PROD.OPRVXREFUID))
    but use it in such a way that it allows for NULL values.

    I am using this left join (see code below) to account for members with NULL HCIDs, but when I apply the above code, the NULLs are left out.

    (I left in the code below the involved table so I could see how to apply the solution to the MAX problem.)
    Code:
    FROM     DB2PROD.TMDTRPTMR TMD
             LEFT OUTER JOIN DB2PROD.OPRVXREFUID OPR1 ON TMD.I_CONTRACT=OPR1.I_TID, 
    ------the Select MAX(D_HCID_END_USE) code needs to apply to the above join------how do I do that?-----
             DB2PROD.TMDTRPPRVSUM TMD1
             LEFT OUTER JOIN DB2PROD.PFTTCPMFDEMO PFT ON TMD1.I_PROVIDER_NUMBER=PFT.I_PROVIDER_NUMBER AND TMD1.C_PLAN=PFT.C_PLAN,
    Thank you for your help!
    Laura

  2. #2
    Join Date
    Jul 2012
    Posts
    31
    I needed to think about it a little longer- I just figured out what I had to do:

    Code:
    AND      (OPR1.D_HCID_END_USE = (SELECT MAX(D_HCID_END_USE) FROM DB2PROD.OPRVXREFUID)
    OR  OPR1.D_HCID_END_USE Is Null)
    It gave me the null values I was looking for.

    Left this in case someone else had a similar situation.

    I am very grateful to have this Forum!


    Laura

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Pleasde publish whole code of your query.

    SELECT ...
    FROM ...
    LEFT OUTER JOIN
    ...
    ... ,
    LEFT OUTER JOIN
    ...
    ...
    WHERE ...
    ...

  4. #4
    Join Date
    Jul 2012
    Posts
    31
    Here is the query. Please do let me know if there is a better way than what I did (the and/or at the bottom of the code). Using the OR is NULL left in the 4 rows that went missing when I didn't have the OR in the code.

    Code:
    SELECT   TMD.I_TRIMED_SYS_KEY, TMD.D_CREATED, TMD.I_CONTRACT, 
              OPR1.I_HCID
    FROM     DB2PROD.TMDTRPTMR TMD
             LEFT OUTER JOIN DB2PROD.OPRVXREFUID OPR1 ON TMD.I_CONTRACT=OPR1.I_TID, 
             DB2PROD.TMDTRPPRVSUM TMD1
             LEFT OUTER JOIN DB2PROD.PFTTCPMFDEMO PFT ON TMD1.I_PROVIDER_NUMBER=PFT.I_PROVIDER_NUMBER AND TMD1.C_PLAN=PFT.C_PLAN, 
             DB2PROD.TMDTRPRPTCAT TMD2 
    WHERE    TMD.C_REPORT_CATG=TMD2.C_REPORT_CATG 
    AND      TMD.C_REPORT_CATGRP=TMD2.C_REPORT_CATGRP 
    AND      TMD.I_TRIMED_SYS_KEY=TMD1.I_TRIMED_SYS_KEY 
    AND      TMD1.I_TMRPROV_SEQ_NUM = 1 
    AND      TMD.I_TRIMED_SYS_KEY in ( '7144050094', '7144074559', '7144060291', '7144060483', '7144061564', '7144048716', '7144053752', '7144056693', '7144053188', '7144055231', '7144067193', '7144051790', '7144062306', '7144066268', '7144059640')
    AND      (OPR1.D_HCID_END_USE = (SELECT MAX(D_HCID_END_USE) FROM DB2PROD.OPRVXREFUID)
    OR  OPR1.D_HCID_END_USE Is Null)

    Thanks-
    Laura

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is my intermediate result.

    It may take too much time for me to explain the rationale.(It is 4 AM in Japan. And I feel very sleepy.)
    I want to add some explanations and some questions tomorrow, if I had time and energy to do that.

    Code:
    SELECT TMD.I_TRIMED_SYS_KEY, TMD.D_CREATED, TMD.I_CONTRACT
         , OPR1.I_HCID
     FROM  DB2PROD.TMDTRPTMR     TMD
     LEFT  OUTER JOIN
           (SELECT *
             FROM  DB2PROD.OPRVXREFUID OPR
             WHERE OPR.D_HCID_END_USE
                   = (SELECT MAX(D_HCID_END_USE) FROM DB2PROD.OPRVXREFUID)
           ) AS OPR1
      ON   OPR1.I_TID = TMD.I_CONTRACT
     WHERE TMD.I_TRIMED_SYS_KEY
           in (  '7144050094', '7144074559', '7144060291', '7144060483', '7144061564', '7144048716', '7144053752' , '7144056693'
               , '7144053188', '7144055231', '7144067193', '7144051790', '7144062306', '7144066268', '7144059640')
       AND EXISTS
           (SELECT 0
             FROM  DB2PROD.TMDTRPPRVSUM  TMD1
             WHERE TMD1.I_TRIMED_SYS_KEY = TMD.I_TRIMED_SYS_KEY
               AND TMD1.I_TMRPROV_SEQ_NUM = 1
           )
       AND EXISTS
           (SELECT 0
             FROM  DB2PROD.TMDTRPRPTCAT  TMD2
             WHERE TMD2.C_REPORT_CATG   = TMD.C_REPORT_CATG
               AND TMD2.C_REPORT_CATGRP = TMD.C_REPORT_CATGRP
           )
    ;

Posting Permissions

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