Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2003
    Posts
    7

    Unanswered: How can I do this conditional Where Clause?

    Just found this forum and have learned a tremendous amount browsing around. I've got a problem I hope someone can help me with.

    Two tables, A and B. Need to join where A.site_id = B.site_id. Not a problem. Here is where the problem lies - Also need to add a condition if A.doc_status is not null then A.doc_status = B.status. If no records are found then use condition B.archive_flag = 'C'.

    Any suggestions? Using DB2 7.2 if that matters.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How can I do this conditional Where Clause?

    Here is part of the answer:

    WHERE a.site_id = b.site_id
    AND (a.doc_status IS NULL OR a.doc_status = b.status)

    However, I don't understand the last bit about the archive_flag. Unless you mean:

    WHERE a.site_id = b.site_id
    AND (a.doc_status IS NULL OR a.doc_status = b.status OR b.archive_flag='C')

  3. #3
    Join Date
    Sep 2003
    Posts
    7

    Re: How can I do this conditional Where Clause?

    Thanks, I got the first part. It's the second part that is confusing both of us. In other words, if the first parts condition produces no rows, then we are going to use a completely different condition, archive_flag, and ignore the first condition.


    Originally posted by andrewst
    Here is part of the answer:

    WHERE a.site_id = b.site_id
    AND (a.doc_status IS NULL OR a.doc_status = b.status)

    However, I don't understand the last bit about the archive_flag. Unless you mean:

    WHERE a.site_id = b.site_id
    AND (a.doc_status IS NULL OR a.doc_status = b.status OR b.archive_flag='C')
    Last edited by GTI337; 09-16-03 at 10:57.

  4. #4
    Join Date
    Nov 2002
    Location
    vienna
    Posts
    9

    Re: How can I do this conditional Where Clause?

    maybe this is the solution

    select a.*,b.*
    from a , b
    where a.site_id=b.site_id and
    (
    a.doc_status is null or
    a.doc_status=b.doc_status or
    (
    b.archive_flag='C' and not exists
    (select 1 from a a2,b b2 where a.site_id=a2.site_id and a2.site_id=b2.site_id and
    (
    a2.doc_status is null or
    a2.doc_status=b2.doc_status
    )
    )
    )
    )
    ;

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How can I do this conditional Where Clause?

    That sounds like a cartesian product - for each row in A where condition1 is false, return ALL rows from B where condition2 is true...?

    Something like:

    Code:
    WHERE a.site_id = b.site_id
    AND (   (a.doc_status IS NULL OR a.doc_status = b.status)
          OR (a.doc_status IS NOT NULL
                AND NOT EXISTS (SELECT 1 FROM b b2
                                           WHERE a.doc_status = b2.status)
                AND b.archive_flag='C')
           )
    Seems very odd...

  6. #6
    Join Date
    Sep 2003
    Posts
    7

    Re: How can I do this conditional Where Clause?

    The code below almost gets it. I guess I need to explain more. My design docs state (Not done by me, BTW):

    "For each record in table A if doc_status is not null then access table B where B.status = A.doc_status. If no record matches then use table B where archive_flag = 'C'."

    Table A is a list of sites. Table B contains demographic studies for the sites. There is more than one demographic study for each site. What they are trying to get is the demographic study for a site where the status is equal to the site status. If that doesn't exist, then we want to bring back the demographic study for that site whose demo study archive_flag is 'C', for current.


    Originally posted by andrewst
    That sounds like a cartesian product - for each row in A where condition1 is false, return ALL rows from B where condition2 is true...?

    Something like:

    Code:
    WHERE a.site_id = b.site_id
    AND (   (a.doc_status IS NULL OR a.doc_status = b.status)
          OR (a.doc_status IS NOT NULL
                AND NOT EXISTS (SELECT 1 FROM b b2
                                           WHERE a.doc_status = b2.status)
                AND b.archive_flag='C')
           )
    Seems very odd...

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: How can I do this conditional Where Clause?

    Third time lucky? :-

    Code:
    WHERE a.site_id = b.site_id
    AND (   (a.doc_status = b.status)
          OR (NOT EXISTS (SELECT 1 FROM b b2
                                    WHERE a.doc_status = b2.status)
                AND b.archive_flag='C')
           )

  8. #8
    Join Date
    Sep 2003
    Posts
    7

    Re: How can I do this conditional Where Clause?

    Thank you! Finally got it to work with some tweaking of the code below. Turns out the design docs were backwards and we also needed to account for the case when only a 'C' record exisited but the statuses did not match . . .

    Originally posted by andrewst
    Third time lucky? :-

    Code:
    WHERE a.site_id = b.site_id
    AND (   (a.doc_status = b.status)
          OR (NOT EXISTS (SELECT 1 FROM b b2
                                    WHERE a.doc_status = b2.status)
                AND b.archive_flag='C')
           )

Posting Permissions

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