Solved

oracle full outer join problem

Posted on 2007-11-18
1
2,796 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
1 Comment
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now