Full Outer Join

Dear Experts -

How can I rewrite the query for full outer join(10g).

I'm getting ora-01416 for the following syntax.

select sec.*, bmk.*
  from (select ibf.sec_id,
               is.symbol,
               is.dur_eff
          from (select ibf.sec_id
                  from ia.ibf_hst ibf
                 where ibf.bmk_id = 'SLAGG') ibf,
               ia.is_fi_hst is
         where ibf.sec_id = is.sd_key) sec,        
       (select ibf.sec_id,
               ibsf.bmk_cusip "BMK Cusip",
               ibh.mv_pct "FI Bmk %MV"
          from ia.ibf_hst      ibf,
               ia.ibsf ibsf,
               ia.ibh  ibh
         where ibf.bmk_id = 'SLAGG'
           and ibf.bmk_sd_key =  ibsf.bmk_sd_key
           and ibf.bmk_sd_key = ibh.bmk_sd_key
           and ibf.bmk_dim_key = ibh.bmk_dim_key) bmk  
 where sec.sec_id = bmk.sec_id(+)
 and  sec.sec_id(+) = bmk.sec_id


Any ideas?

Thanks a lot for your time.
LVL 18
sventhanAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should o:
select sec.*, bmk.*
  from (select ibf.sec_id,
               is.symbol,
               is.dur_eff
          from (select ibf.sec_id
                  from ia.ibf_hst ibf
                 where ibf.bmk_id = 'SLAGG') ibf,
               ia.is_fi_hst is
         where ibf.sec_id = is.sd_key) sec
FULL OUTER JOIN         
       (select ibf.sec_id,
               ibsf.bmk_cusip "BMK Cusip",
               ibh.mv_pct "FI Bmk %MV"
          from ia.ibf_hst      ibf,
               ia.ibsf ibsf,
               ia.ibh  ibh
         where ibf.bmk_id = 'SLAGG'
           and ibf.bmk_sd_key =  ibsf.bmk_sd_key
           and ibf.bmk_sd_key = ibh.bmk_sd_key
           and ibf.bmk_dim_key = ibh.bmk_dim_key
  ) bmk   
 ON sec.sec_id = bmk.sec_id

Open in new window

0
 
paquicubaConnect With a Mentor Commented:
select sec.*, bmk.*
  from (select ibf.sec_id,
               i.symbol,
               i.dur_eff
          from (select ibf.sec_id
                  from ia.ibf_hst ibf
                 where ibf.bmk_id = 'SLAGG') ibf,
               ia.is_fi_hst i
         where ibf.sec_id = i.sd_key) sec
         
         FULL OUTER JOIN
         
       (select ibf.sec_id,
               ibsf.bmk_cusip "BMK Cusip",
               ibh.mv_pct "FI Bmk %MV"
          from ia.ibf_hst      ibf,
               ia.ibsf ibsf,
               ia.ibh  ibh
         where ibf.bmk_id = 'SLAGG'
           and ibf.bmk_sd_key =  ibsf.bmk_sd_key
           and ibf.bmk_sd_key = ibh.bmk_sd_key
           and ibf.bmk_dim_key = ibh.bmk_dim_key) bmk
           
ON (sec.sec_id = bmk.sec_id and sec.sec_id = bmk.sec_id)
0
 
paquicubaCommented:
your problem is NOT when you try to use the FULL OUTER JOIN syntax, your problem is "IS", yes, you're trying to put an alias on a table using a reserved word ("IS")

ia.is_fi_hst is <------- Wrong
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sventhanAuthor Commented:
I do not want to use UNION.
0
 
sventhanAuthor Commented:
Thanks for the quick updates angel and Alex.
I'll test it and get back to you.
 
0
 
sventhanAuthor Commented:
Alias is not a problem when I modifed it from the original I messed it up.
Thanks again.
It works and I'll award the points soon.
0
 
sventhanAuthor Commented:
Do you have any idea how to make this to work in Oracle 9i?
If not, thats just fine.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
oracle 9i also supports the full outer join.
0
All Courses

From novice to tech pro — start learning today.