I have two tables joined together as a left outer join. Table A may or may not be related to records in table B and they have a variety of relationship types. I want to show all records in table A (whether there is a relationship or not) and only those in table B where relationship type = a specific value.

This message has been edited. Last edited by: <Kathryn Henning>,

Melissa,

Have you tried to add a "where clause" (conditional join) to your join; such as:

WHERE B.TableB_Field = Spec_Value

Thanks

Luiz

Yes I did put the where clause on there, but then I don't get the records that don't have the relationship at all. I need to have both.

There may be a 'correct' answer, but in this situation I would do 2 extracts using each set of rule and then merge the hold files together to create the whole extract. So I would do a normal join and extract the data with tablea-tableb match, then a separate left_outer join and extract the data where tableb data eq missing. Then merge the 2 hold files together with a 'table file' and 'more'.

Sorry - I didn't notice this was in the infoassist forum so answered the question with a 'dev studio' hat on. Good luck with the problem.

If you are using a LEFT_OUTER Join and have a WHERE clause with a specific value then any MISSING (ie NULL) values are automatically excluded. To bring back in those without a relationship try

WHERE B.TableB_Field EQ Spec_Value AND B.TableB_Field EQ MISSING

You may also need to add

SET ALL=PASS

depending on the adapter you are using

Cheers

Stu