?
Solved

Full Outer Join

Posted on 2008-10-07
8
Medium Priority
?
2,125 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and theā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

752 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