May 24, 2006 at 2:09 pm
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'.
May 24, 2006 at 2:16 pm
"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?
May 24, 2006 at 2:24 pm
"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.
May 24, 2006 at 2:51 pm
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'
May 24, 2006 at 8:54 pm
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