How can I do this?

  • Here is my sql statement:

    SELECT BL_IE.key_no,     

    BL_IE.proc_date_time,   

    BL_IE.error_count,

    BL_IE.origin_code, 

    BL_IE.excep_message

    FROM BL_IE,   BL_D

    WHERE ( BL_IE.key_no *= BL_D.key_no )

    This brings back a bunch of records and ALL revisions. On BL_D, I have a rev_no column (revision number) and a max_rev_flag column. What I want to do is....

    If there is a record in BL_D that matches the BL_IE.key_no, then I ONLY want those records returned from BL_D that have a max rev_no or max_rev_flag = 'Y'.

  • "then I ONLY want those records returned from BL_D that have a max rev_no or max_rev_flag = 'Y'."

    Should this read, max_rev_no or max_rev_flag = 'Y'?  Are you looking for BL_D rows where the max_rev_no IS NOT NULL or the max_rev_flag is equal to Y?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • "Should this read, max_rev_no or max_rev_flag = 'Y'?  Are you looking for BL_D rows where the max_rev_no IS NOT NULL or the max_rev_flag is equal to Y?"

    If there are rows in BL_D, then I only want those rows where max_rev_flag = 'Y'.

    "Are you looking for BL_D rows where the max_rev_no IS NOT NULL or the max_rev_flag is equal to Y?"

    Sorry, there is no max_rev_no - I should have said Max(rev_no) - which I guess is the same as max_rev_flag = 'Y'. rev_no and max_rev_flag are never NULL in BL_D.

  • This should return all rows from BL_IE and only rows from BL_D where the max_rev_flag = 'Y'. 

    SELECT BL_IE.key_no,  

        BL_IE.proc_date_time,  

        BL_IE.error_count,

        BL_IE.origin_code, 

        BL_IE.excep_message

    FROM BL_IE

        LEFT JOIN BL_D

        ON BL_IE.key_no = BL_D.key_no and BL_D.max_rev_flag = 'Y'

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • In the full version of your query, are you retrieving any fields from BL_D?

    If not, are you using the join to BL_D to produce duplicate records in your result set? Since you aren't retrieving any fields from it, that is the only difference it can make to the results, being a left join. If you want to restrict the results based on the records you will need to do something different.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply