Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    9

    Unanswered: Joining to status table needing MaxID only IF status = 'O'

    Joining to a table with multiple rows needing only the max key if status = 'O'.

    The problem I am having is when I use Max(STA_ID) and I can't use where clause for STA_CD = 'O'. I only want to join "IF" the MAX(STA_ID) has Status code of 'O'.

    Thanks in Advance, Marcel K. Data below would not yield a join, because the MAX(STA_ID) STA_CD = 'C'.


    ID_NO STA_ID STA_CD
    1085614000006 1999-03-18-23.16.39.945269 C
    1085614000006 1999-03-18-11.59.40.284873 O
    1085614000006 1999-03-17-10.39.20.630029 O
    1085614000006 1999-03-11-06.57.31.405619 O

    DB2 Version is 7.1.1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think this is what you want:

    Assumptions:
    tableA = parent table
    TableB as described in your post

    select ... from tableA as A, tableB as b
    where (a.ID_NO = b.ID_NO) and (b.sta_ID =
    (select max(b2.sta_ID) from tableB as B2 where (b2.ID_NO = a.ID_NO)) and (b.STA_CD = 'O')

    HTH

    Andy

  3. #3
    Join Date
    Nov 2003
    Posts
    9
    If it helps, table A to table be is a one to many. I tried running the suggestion and I was getting no rows back. I only want to join when the max record is 'O', not the max record with 'O'. Keyword here is IF it has an 'O' as in open.

    thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Woops, I missed a closing parenthesis ")"

    try:

    select ... from tableA as A, tableB as b
    where (a.ID_NO = b.ID_NO) and (b.sta_ID =
    (select max(b2.sta_ID) from tableB as B2 where (b2.ID_NO = a.ID_NO))) and (b.STA_CD = 'O')

    Andy

  5. #5
    Join Date
    Nov 2003
    Posts
    9
    Still not flying it doesn't error out, but no data is coming back.

    Thanks for your input.

  6. #6
    Join Date
    Nov 2003
    Posts
    9

    Sample data had 3 columns, not looking like three in initial post.

    ID_NO STA_ID STA_CD
    1085614000006 1999-03-18-23.16.39.945269 C
    1085614000006 1999-03-18-11.59.40.284873 O
    1085614000006 1999-03-17-10.39.20.630029 O
    1085614000006 1999-03-11-06.57.31.405619 O

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The way I understand what you want is this. Show the ID_NO if the row in table B with the max(STA_ID) has a STA_CD value of 'O', otherwise do not show th ID_NO. If this is the case, then with the data you have shown, you will get nothing back because the max(sta_cd) has a 'C". Now if you want the 2nd row returned, then the query would be:

    select ... from tableA as A, tableB as b
    where (a.ID_NO = b.ID_NO) and (b.sta_ID =
    (select max(b2.sta_ID) from tableB as B2 where (b2.ID_NO = a.ID_NO) and (b2.STA_CD = 'O'))) and (b.STA_CD = 'O')

    Andy

Posting Permissions

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