Home » SQL & PL/SQL » SQL & PL/SQL » How to merge data from two temp tables in 'with' clause (How to merge data from two temp tables in 'with' clause)
How to merge data from two temp tables in 'with' clause [message #275162] Thu, 18 October 2007 11:12 Go to next message
Vackar
Messages: 81
Registered: October 2007
Member
Hi,

I am using the with clause to generate some temp queriable resources.

one produces the following
----------------------------
Unique Name    X_Value       
-------------  --------
U1                8
U2                5
U3                6
U4                7
----------------------------


and the other produces
----------------------------
Unique Name    Y_Value       
-------------  --------
U2                16
U3                99
U4                108
----------------------------


and I am trying to get a table which looks like this

--------------------------------------------------------
Unique Name    X_Value    Y_Value     
-------------  --------  -----------
U1                8           0
U2                5           16
U3                6           99
U4                7           108
--------------------------------------------------------

Any help would be much appreciated.

Thanks,
Vackar

Re: How to merge data from two temp tables in 'with' clause [message #275163 is a reply to message #275162] Thu, 18 October 2007 11:20 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
There are any number of solutions to produce the information you want. Unfortunately, without some additional details, it's kinda hard to advise you on what to do. For example, is this from one table or two? What are the fields in the table(s)? What do the queries look like that produce what you've posted? Post some more detail to get the assistance you want.
Re: How to merge data from two temp tables in 'with' clause [message #275171 is a reply to message #275163] Thu, 18 October 2007 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also post your Oracle version.

Regards
Michel
Re: How to merge data from two temp tables in 'with' clause [message #275229 is a reply to message #275162] Thu, 18 October 2007 22:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
maybe you are looking for something like this:

with
   table1 as ()
  ,table2 as ()
  ,table3 as (
               select key from table1 union
               select key from table2
             )
select table3.key
      ,table1.*
      ,table2.*
where table3.key  = table1.key (+)
and table3.key  = table1.key (+)
/


This is an old concept, updated to use new techniques. Understand the joins here, and then you figure out what column expressions you need to select for your final result.

Good luck, Kevin
Re: How to merge data from two temp tables in 'with' clause [message #275256 is a reply to message #275229] Fri, 19 October 2007 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe:
select nvl(t1.key,t2.key) key,
       nvl(t1.x_value,0) x_value,
       nvl(t2.y_value,0) y_value
from t1 full outer join t2 on (t1.key=t2.key)
/

Regards
Michel
Re: How to merge data from two temp tables in 'with' clause [message #275283 is a reply to message #275256] Fri, 19 October 2007 03:58 Go to previous messageGo to next message
Vackar
Messages: 81
Registered: October 2007
Member
Michel Cadot wrote on Fri, 19 October 2007 01:24

Or maybe:
select nvl(t1.key,t2.key) key,
       nvl(t1.x_value,0) x_value,
       nvl(t2.y_value,0) y_value
from t1 full outer join t2 on (t1.key=t2.key)
/

Regards
Michel




THANKS!!! That was just what I was lookig for! Smile
Re: How to merge data from two temp tables in 'with' clause [message #275357 is a reply to message #275283] Fri, 19 October 2007 11:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
show off

I never did learn ansi syntax. maybe it is time.

Kevin
Re: How to merge data from two temp tables in 'with' clause [message #275365 is a reply to message #275357] Fri, 19 October 2007 12:01 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Full outer join is the only ANSI thing I use as this is the only thing we can't do with Oracle syntax.

Regards
Michel
Previous Topic: Error while refreshing materialized View through DBMS_MVIEW
Next Topic: Compare two tables
Goto Forum:
  


Current Time: Sun Dec 04 23:01:59 CST 2016

Total time taken to generate the page: 0.08168 seconds