[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Full Outer Join

Posted on 2008-10-07
8
Medium Priority
?
2,134 Views
Last Modified: 2012-05-05
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.
0
Comment
Question by:sventhan
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 22659886
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
 
LVL 23

Assisted Solution

by:paquicuba
paquicuba earned 1000 total points
ID: 22659909
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 22659938
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 18

Author Comment

by:sventhan
ID: 22659961
I do not want to use UNION.
0
 
LVL 18

Author Comment

by:sventhan
ID: 22659967
Thanks for the quick updates angel and Alex.
I'll test it and get back to you.
 
0
 
LVL 18

Author Comment

by:sventhan
ID: 22660178
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
 
LVL 18

Author Comment

by:sventhan
ID: 22660204
Do you have any idea how to make this to work in Oracle 9i?
If not, thats just fine.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22660238
oracle 9i also supports the full outer join.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question