Home » SQL & PL/SQL » SQL & PL/SQL » Merge 2 tables
icon2.gif  Merge 2 tables [message #233619] Thu, 26 April 2007 23:12 Go to next message
kinwho
Messages: 6
Registered: April 2007
Location: Bay area
Junior Member
I have 2 tables, forecast and actual, and have the same structure.

Table : ACTUAL
fiscal_year quarter value
----------- ------- -----
2006 1 1000
2006 2 200
2006 3 0
2006 4 300

Table: FORECAST
fiscal_year quarter value
----------- ------- -----
2006 3 200
2006 4 300
2007 1 500
2007 2 500

Can we generate a table contains following data:

fiscal_year quarter value forecast_value
----------- ------- ----- --------------
2006 1 1000
2006 2 200
2006 3 0 200
2006 4 300 300
2007 1 500
2007 2 500

Is that possible to generate as a materialized view, so that the data can be refreshed? Thanks in advance.

Kin
Re: Merge 2 tables [message #233622 is a reply to message #233619] Thu, 26 April 2007 23:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
A simple outer join will do this.
Re: Merge 2 tables [message #233651 is a reply to message #233622] Fri, 27 April 2007 01:52 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Frank i tried lot but i am not able can you please post the ans.

i tried some thing like that ..but i am not able

select t2.iscal_year,t1.quarter,t1.value,t2.value from test1 t1,test2 t2  where t1.ISCAL_YEAR(+)=t2.ISCAL_YEAR and t1.quarter(+)= t2.quarter
union
select t2.iscal_year,t2.quarter,t1.value,t2.value from test1 t1,test2 t2  where t1.ISCAL_YEAR(+)=t2.ISCAL_YEAR and t1.quarter(+)= t2.quarter




--Yash

--Yash
Re: Merge 2 tables [message #233656 is a reply to message #233651] Fri, 27 April 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "full outer join".

Regards
Michel
Re: Merge 2 tables [message #233669 is a reply to message #233656] Fri, 27 April 2007 02:36 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
i am really sorry michel i chnage my query like that...


select d.year1,d.qty,d.aval,d.fcost from (SELECT t1.ISCAL_YEAR year1,t1.QUARTER qty,t1.value aval,t2.value fcost from test1 t1 full outer join test2 t2 on (t1.ISCAL_YEAR=t2.ISCAL_YEAR and t1.QUARTER=t2.QUARTER)
union 
sELECT t2.ISCAL_YEAR year1,t2.QUARTER qty,t1.value aval,t2.value fcost from test1 t1 full outer join test2 t2 on (t1.ISCAL_YEAR=t2.ISCAL_YEAR and t1.QUARTER=t2.QUARTER)) d where d.year1 is not null



i am getting output like this


22006,1,1000,
2006,2,200,
2006,3,0,200
2006,4,3000,300
2007,1,,500
2007,2,,500

but kinwho want output like this

2006 1 1000
2006 2 200
2006 3 0 200
2006 4 300 300
2007 1 500
2007 2 500


can u suggest some thing ..


--Yash
Re: Merge 2 tables [message #233676 is a reply to message #233669] Fri, 27 April 2007 02:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You cannot get a variable list of columns in one resultset. Not one row with three columns and one row with four.
Re: Merge 2 tables [message #233688 is a reply to message #233676] Fri, 27 April 2007 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ BREAK YOUR LINES. At most 80 characters. No one is able to read lines of 300 characters. Maybe this is the reason you can't find the query and you made so much typos in what you wrote (see the ISCAL_YEAR)
2/
SELECT nvl(t1.fiscal_year,t2.fISCAL_YEAR) year,
       nvl(t1.quarter,t2.QUARTER) quarter,
       t1.value aval, 
       t2.value fcost 
from test1 t1 full outer join test2 t2 
     on (t1.fISCAL_YEAR=t2.fISCAL_YEAR and t1.QUARTER=t2.QUARTER)
/

Regards
Michel

Re: Merge 2 tables [message #233696 is a reply to message #233688] Fri, 27 April 2007 03:20 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Michle ..i will take care in future
Re: Merge 2 tables [message #234000 is a reply to message #233696] Sat, 28 April 2007 18:04 Go to previous message
kinwho
Messages: 6
Registered: April 2007
Location: Bay area
Junior Member
Sorry for the missing information. I am using Oracle 8.1.7, so the full outer join code won't work.

The only way that I could use is:

select t2.fiscal_year,t1.quarter,t1.value,t2.value
from test1 t1,test2 t2
where t1.FISCAL_YEAR(+)=t2.FISCAL_YEAR
and t1.quarter(+)= t2.quarter
union
select t2.fiscal_year,t2.quarter,t1.value,t2.value
from test1 t1,test2 t2
where t1.FISCAL_YEAR=t2.FISCAL_YEAR (+)
and t1.quarter= t2.quarter (+)

Unfortunately, I just find out my table structure will be changed to the following:

Table : ACTUAL
fiscal_year quarter dept value
----------- ------- ---- -----
2006 1 A 400
2006 1 A 600
2006 2 A 200
2006 3 A 0
2006 4 A 300

Table: FORECAST
fiscal_year quarter dept case value
----------- ------- ---- ---- -----
2006 3 A 1 200
2006 4 A 1 300
2007 1 A 1 500
2007 2 A 1 500
2007 1 B 1 100
2007 2 B 1 200
2007 1 B 2 200
2007 2 B 2 200

Can we generate a table contains following data:

fiscal_year quarter dept case1 value forecast_value1 case2 f_val2
----------- ------- ---- ----- ----- --------------- ----- ------
2006 1 A 1 1000
2006 2 A 1 200
2006 3 A 1 0 200
2006 4 A 1 300 300
2007 1 A 1 500
2007 2 A 1 500
2007 1 B 1 100 2 200
2007 2 B 1 200 2 200

With the actual table will eventually get the summation for the total value, the forecast may eventually be changed into that direction as well, the outer join will be on the 2 dynamic tables. Since these data may be synchronized again every month/quarter, will outer join be a better option, or I should consider to use the PL/SQL to sync the sumary table.
Any suggestion. Thanks in reponse.
Previous Topic: Help with Select statement..
Next Topic: Do you think select statement on the matererialed view is faster than on the table?
Goto Forum:
  


Current Time: Wed Dec 07 20:08:27 CST 2016

Total time taken to generate the page: 0.12519 seconds