Solved

oracle full outer join problem

Posted on 2007-11-18
1
2,806 Views
Last Modified: 2013-12-19
SQL> select * from test1;

STATE     ID            NAME                 PARAM         count1
---------- ---------- -------------------- ---------- ----------
AK         1234       testname1            bo                 1
AK         1235       testname2            bo                 1
MA         1234       testname1            ao                 1
AK         1234       testname1            ao                 2
MA         1235       testname5            ao                 3
testbo  is view created from table test1 for param bo
SQL> select * from testbo;
  
STATE      ID          NAME                 PARAM          count1
---------- ---------- -------------------- ---------- ----------
AK         1234       testname1            bo                 1
AK         1235       testname2            bo                 1
 
 
SQL> select * from testao; this is view for param ao
 
STATE      ID     NAME                 PARAM              count1
---------- ---------- -------------------- ---------- ----------
MA         1234       testname1            ao                 1
AK         1234       testname1            ao                 2
MA         1235       testname5            ao                 3
 
 
 
SQL> select a.state,a.id,a.name,a.count1 as bo,b.count1 as ao from
testbo a full outer join testao b on a.state =b.state and a.name =b.name and a.id = b.id
    
STATE      ID            NAME                        bo       ao
---------- ---------- -------------------- ---------- ----------
AK         1234       testname1                     1          2
AK         1235       testname2                     1
                                                               1
                                                               3
 
 
How to display state ,ID,name values for ao as it was displaying only count1.
 
Best Regards,

Open in new window

0
Comment
Question by:pk143143
[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
1 Comment
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 20310471
you have to coalesce the fields:

SQL> select coalesce(a.state, b.state) state
  ,coalesce(a.id, b,id) id
 ,coalesce(a.name, b.name) name
,a.count1 as bo
,b.count1 as ao 
from testbo a 
full outer join testao b 
   on a.state =b.state 
  and a.name =b.name 
  and a.id = b.id
 ;

Open in new window

0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

687 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