Using Outer Joins with substr...instr
825928Dec 22 2010 — edited Dec 22 2010Hi Oracle!
Im trying to outer join 2 tables as below, but get an error stating 'a table may be outer joined to at most one other table'.
What am I doing wrong?
select distinct
rc.case_id
from
apr_mart.rpt_case rc,
apr_mart.rpt_product rp,
apr_mart.dm_lm_product lp1,
apr_mart.dm_lm_product lp2
where
rc.case_id = rp.case_id and
rc.protocol_num = substr(lp1.drug_code(+),1,instr(lp1.drug_code||'.','.')-1) and
rp.co_drug_code = lp2.drug_code (+) and
(
lp2.psur_group_name in ('TEST') or
lp1.psur_group_name in ('TEST')
)
;
Removing the 2nd outer join gives the same message.
Many thanks!
Rory