Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Full Outer Join

Posted on 2008-10-07
8
Medium Priority
?
2,129 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

618 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