Hi.

This is more for my own development than for anything else as I already have a solution that works. I'm sure there is a better solution using ansi join syntax but I just can't get it.
This is the select statement that works:

I basically don't use outer joins at all. It's just a union of two statements where the first returns all the records except those that will be returned in the second:

select t2.ROLE_CD, ''
from SIEBEL.S_ORG_EXT t1,
SIEBEl.S_ACCNT_POSTN t2,
SIEBEL.S_CONTACT t4,
SIEBEL.S_ORG_EXT t5,
SIEBEL.S_POSTN t3
where t1.ROW_ID = t2.OU_EXT_ID
and t2.POSITION_ID = t3.ROW_ID
and t3.PR_EMP_ID = t4.ROW_ID
and t1.BU_ID = t5.ROW_ID
and t1.ROW_ID = '1-F3-2990'
and not exists (
select 1
from SIEBEL.S_ORG_EXT t1,
SIEBEl.S_ACCNT_POSTN t2x,
SIEBEL.S_CONTACT t4,
SIEBEL.S_ORG_EXT t5,
SIEBEL.S_POSTN t3,
SIEBEl.S_ORG_EXT par_acc,
SIEBEl.S_ACCNT_POSTN par_acc_pos
where t1.ROW_ID = t2x.OU_EXT_ID
and t2x.POSITION_ID = t3.ROW_ID
and t3.PR_EMP_ID = t4.ROW_ID
and t1.BU_ID = t5.ROW_ID
and par_acc.row_id = t1.PAR_OU_ID
and par_acc_pos.OU_EXT_ID = par_acc.row_id
and par_acc_pos.position_id = t3.ROW_ID
and t2.row_id = t2x.row_id)
union
select t2.ROLE_CD, par_acc_pos.ROLE_CD
from SIEBEL.S_ORG_EXT t1,
SIEBEl.S_ACCNT_POSTN t2,
SIEBEL.S_CONTACT t4,
SIEBEL.S_ORG_EXT t5,
SIEBEL.S_POSTN t3,
SIEBEl.S_ORG_EXT par_acc,
SIEBEl.S_ACCNT_POSTN par_acc_pos
where t1.ROW_ID = t2.OU_EXT_ID
and t2.POSITION_ID = t3.ROW_ID
and t3.PR_EMP_ID = t4.ROW_ID
and t1.BU_ID = t5.ROW_ID
and t1.PAR_OU_ID = par_acc.row_id
and par_acc.row_id = par_acc_pos.OU_EXT_ID
and t2.position_ID = par_acc_pos.position_id
and t1.ROW_ID = '1-F3-2990'

I think the following should return the same result but I run in to the error message "table may be outer joined to at most one other table":

select t2.ROLE_CD,
par_acc_pos.ROLE_CD
from SIEBEL.S_ORG_EXT t1,
SIEBEl.S_ACCNT_POSTN t2,
SIEBEL.S_CONTACT t4,
SIEBEL.S_ORG_EXT t5,
SIEBEL.S_POSTN t3,
SIEBEl.S_ORG_EXT par_acc,
SIEBEl.S_ACCNT_POSTN par_acc_pos
where t1.ROW_ID = t2.OU_EXT_ID
and t2.POSITION_ID = t3.ROW_ID
and t3.PR_EMP_ID = t4.ROW_ID
and t1.BU_ID = t5.ROW_ID
and t1.PAR_OU_ID = par_acc.row_id(+)
and par_acc.row_id = par_acc_pos.OU_EXT_ID(+)
and t2.position_ID = par_acc_pos.position_id(+)
and t1.ROW_ID = '1-F3-2990'

The final "and t1.ROW_ID = '1-F3-2990'" is just to limit my results to see if it's working. I know that it's possible to avoid the above error by using ansi syntax but after hours of trying and searching the net I still can't get it right.

Any suggestions would be greatly appreciated.

Thanks
Con