• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2822
  • Last Modified:

oracle full outer join problem

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
pk143143
Asked:
pk143143
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now