oracle full outer join problem

Posted on 2007-11-18
Medium Priority
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
How to display state ,ID,name values for ao as it was displaying only count1.
Best Regards,

Open in new window

Question by:pk143143
1 Comment
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 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


Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
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.

627 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