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?

Improve company productivity with a Business Account.Sign Up

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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.