Merge different data sets from same table using UNION

A UNION doesn't have a JOIN syntax.  When you query two tables you JOIN them.  A UNION is two independant queries that combines two results sets.  Different animals.

Keep this handy, it is a venn diagram that explains joins:
http://dkmathstats.com/connecting-sql-joins-set-theory-concepts/

A UNION is the same as a FULL OUTER JOIN.

All that said:
I don't know what you want.  Are you looking to call two different procedures or create a new one that does the SQL?

I'm also not following the subtraction you want.

If you can come up with a simple test case that has sample data and expected results, we can likely provide working and tested SQL.  You can take that and apply it back to your tables.

We don't need an exact match for all the columns.  Just enough or you to understand the concept we provide.

Thanks for detailed explanation.  

Here's the scenario

PackageA
...
...

ProcedureA
-- Insert into table 1
-- from select conditions..
.. commit;
end procedureA;


Procedure B
Insert into table1
as
select * from different conditions...

Commit;


-- NEW PROC C
Proc_C
-- MERGED DATA FROM BOTH SETS.

Select allOpen in new window

The intent was to merge the 2 data sets  with a UNION in mind.  Are you suggesting a Full outer join as a better option?
The actual merge will be in a 3rd procedure inside a package.


Sample data

Data Set 1:
REPCODE    REPNAME   TRADEDATE  Channel Total
1                   REP1              1/1/2010        5000
3                   REP3              1/3/2010        7000
5                   REP5              1/5/2010        7000
7                   REP7               1/7/2010       18000


Data Set 2:
REPCODE    REPNAME   TRADEDATE  ENERGY Total
2                   REP2              1/2/2010        4000
4                   REP3              1/4/2010        6000
6                   REP6             1/6/2010        10000
7                   REP7               1/7/2010       10000
8                  REP8               1/8/2010       15000


RESULT SET would some thing like the below
REPCODE    REPNAME   TRADEDATE   NET TOTAL
1                   REP1              1/1/2010        5000
2                   REP2              1/2/2010        4000
3                   REP3              1/3/2010        7000-6000 = 1000
4                   REP4              1/4/2010        7000
5                   REP5              1/5/2010        7000
6                   REP6             1/6/2010        10000
7                   REP7               1/7/2010       18000-10000 = 8000
8                  REP8               1/8/2010       15000

Select allOpen in new window

ooo steve didn't put that right ...
with a full outer join, columns of A will be before colums of B
with a union, rows of A will be above rows of B

a full outer join, joins columns
a union, joins rows

A union query does exactly what you asked for:"Merge different data sets from same [or different] table[s]".  But, there is no "join" clause either allowed or required in a "union" query.

I think your "union" query needs to look like this (very close to what you posted, with a difference on the last line, no "join" clause, and "UNION ALL" instead of "UNION"):

SELECT
             l.FIRSTDAY,
             l.LASTDAY,
             l.STARTTIME,
             l.STOPTIME,
             l.ESIID,
             l.dunsnumber as DUNSNUMBER,
             l.origin AS ORIGIN, 
             l.status AS STATUS,
             l.repcode AS REPCODE, 
             l.repname AS REPNAME, 
             to_char(l.starttime,'MM/DD/YYYY') TRADEDATE,
             l.intervaldatasource as INTERVALDATASOURCE,
             l.INTV1,l.INTV2,l.INTV3,l.INTV4,l.INTV5,l.INTV6,l.INTV7,l.INTV8,l.INTV9,l.INTV10,l.INTV11,l.INTV12,l.INTV13,
             l.INTV14,l.INTV15,l.INTV16,l.INTV17,l.INTV18,l.INTV19,l.INTV20,l.INTV21,l.INTV22,l.INTV23,l.INTV24,l.INTV25,
             l.INTV26,l.INTV27,l.INTV28,l.INTV29,l.INTV30,l.INTV31,l.INTV32,l.INTV33,l.INTV34,l.INTV35,l.INTV36,l.INTV37,
             l.INTV38,l.INTV39,l.INTV40,l.INTV41,l.INTV42,l.INTV43,l.INTV44,l.INTV45,l.INTV46,l.INTV47,l.INTV48,l.INTV49,
             l.INTV50,l.INTV51,l.INTV52,l.INTV53,l.INTV54,l.INTV55,l.INTV56,l.INTV57,l.INTV58,l.INTV59,l.INTV60,l.INTV61,
             l.INTV62,l.INTV63,l.INTV64,l.INTV65,l.INTV66,l.INTV67,l.INTV68,l.INTV69,l.INTV70,l.INTV71,l.INTV72,l.INTV73,
             l.INTV74,l.INTV75,l.INTV76,l.INTV77,l.INTV78,l.INTV79,l.INTV80,l.INTV81,l.INTV82,l.INTV83,l.INTV84,l.INTV85,
             l.INTV86,l.INTV87,l.INTV88,l.INTV89,l.INTV90,l.INTV91,l.INTV92,l.INTV93,l.INTV94,l.INTV95,l.INTV96,l.INTV97,
             l.INTV98,l.INTV99,l.INTV100           
            FROM RP_REC_LOAD_EXTRACT l JOIN RP_REC_LOAD_EXTRACT E ON (l.repcode = e.repcode)
           WHERE l.intervaldatasource =  'LSCHANNEL'
           UNION ALL
           SELECT
             E.FIRSTDAY,
             E.LASTDAY,
             E.STARTTIME,
             E.STOPTIME,
             E.ESIID,
             E.dunsnumber as DUNSNUMBER,
             E.origin AS ORIGIN, 
             E.status AS STATUS,
             E.repcode AS REPCODE, 
             E.repname AS REPNAME, 
             to_char(e.starttime,'MM/DD/YYYY') TRADEDATE,
             e.intervaldatasource as INTERVALDATASOURCE,
             e.INTV1,e.INTV2,e.INTV3,e.INTV4,e.INTV5,e.INTV6,e.INTV7,e.INTV8,e.INTV9,e.INTV10,e.INTV11,e.INTV12,e.INTV13,
             e.INTV14,e.INTV15,e.INTV16,e.INTV17,e.INTV18,e.INTV19,e.INTV20,e.INTV21,e.INTV22,e.INTV23,e.INTV24,e.INTV25,
             e.INTV26,e.INTV27,e.INTV28,e.INTV29,e.INTV30,e.INTV31,e.INTV32,e.INTV33,e.INTV34,e.INTV35,e.INTV36,e.INTV37,
             e.INTV38,e.INTV39,e.INTV40,e.INTV41,e.INTV42,e.INTV43,e.INTV44,e.INTV45,e.INTV46,e.INTV47,e.INTV48,e.INTV49,
             e.INTV50,e.INTV51,e.INTV52,e.INTV53,e.INTV54,e.INTV55,e.INTV56,e.INTV57,e.INTV58,e.INTV59,e.INTV60,e.INTV61,
             e.INTV62,e.INTV63,e.INTV64,e.INTV65,e.INTV66,e.INTV67,e.INTV68,e.INTV69,e.INTV70,e.INTV71,e.INTV72,e.INTV73,
             e.INTV74,e.INTV75,e.INTV76,e.INTV77,e.INTV78,e.INTV79,e.INTV80,e.INTV81,e.INTV82,e.INTV83,e.INTV84,e.INTV85,
             e.INTV86,e.INTV87,e.INTV88,e.INTV89,e.INTV90,e.INTV91,e.INTV92,e.INTV93,e.INTV94,e.INTV95,e.INTV96,e.INTV97,
             e.INTV98,e.INTV99,e.INTV100 
            FROM REGION e WHERE E.intervaldatasource =  'ENERGY';

Select allOpen in new window

The second part of your question requires a bit more thinking.  Maybe someone else will have a good suggestion for that.  If not, I may have an idea a bit later.

I'm a little confused regarding your problem. It appears that ProcA is getting data from a region_extract table where the intervaldatasource = 'CHANNEL' and ProcB is getting data from a region table where the intervaldatasource = 'ENERGY' and both are getting a sum of totals that are grouped by 112 other fields (assuming that totals is itself a field in the two tables). However, your [invalid] join statement shows data from a table named rp_rec_load_extract where the intervaldatasource = 'LSCHANNEL' being joined with data from the region table without any filter on the intervaldatasource. I assume you want to get a difference in the totals but I can't tell whether that's going to be 112 differences in the sum of the totals, or a difference in the total sum of the totals for each set of data, or what. As had been suggested, if you could provide a reasonable sample of relevant data for each table (include the table structures), perhaps 100 rows and limit the number of INTV fields to say 6 or 7, along with your expected results it would be most helpful to the experts in providing you with a viable and tested solution. Please post any examples as text (i.e. not pictures) so that test cases can be easily created.

@Geert, thanks for the clarification.  Should have caught that.

>>Here's the scenario

Just confirming that procA and procB insert into different tables.

>>Sample data

There are problems with the data.  

For example:
1: there is no 'REP4' in either of the data sets.  
2: the original SQL shows a join attempt on repcode but the new expected results cannot.
   this result:  3                   REP3              1/3/2010        7000-6000 = 1000
   there is no repcode = 3 in dataset 2.

That said, I tweaked the sample data to what I think you meant.

I think this produces the results you want.  If not, please modify my posted test case and provide new expected results and I'll tweak it.

I also want to confirm procA and procB insert into different tables.  If the datasets come from the same table, you might not need the UNION ALL and other "magic".  It might be simpler.

My test case:

drop table tab1 purge;
create table tab1(REPCODE number, REPNAME char(4), TRADEDATE date, Channel_Total number);

insert into tab1 values(1,'REP1',to_date('1/1/2010','MM/DD/YYYY'),5000);
insert into tab1 values(3,'REP3',to_date('1/3/2010','MM/DD/YYYY'),7000);
insert into tab1 values(5,'REP5',to_date('1/5/2010','MM/DD/YYYY'),7000);
insert into tab1 values(7,'REP7',to_date('1/7/2010','MM/DD/YYYY'),18000);

drop table tab2 purge;
create table tab2(REPCODE number, REPNAME char(4), TRADEDATE date, energy_Total number);

insert into tab2 values(2,'REP2',to_date('1/2/2010','MM/DD/YYYY'),4000);
insert into tab2 values(3,'REP3',to_date('1/4/2010','MM/DD/YYYY'),6000);
insert into tab2 values(6,'REP6',to_date('1/6/2010','MM/DD/YYYY'),10000);
insert into tab2 values(7,'REP7',to_date('1/7/2010','MM/DD/YYYY'),10000);
insert into tab2 values(8,'REP8',to_date('1/8/2010','MM/DD/YYYY'),15000);
commit;


/*
RESULT SET would some thing like the below
REPCODE REPNAME TRADEDATE NET TOTAL
1 REP1 1/1/2010 5000
2 REP2 1/2/2010 4000
3 REP3 1/3/2010 7000-6000 = 1000
4 REP4 1/4/2010 7000
5 REP5 1/5/2010 7000
6 REP6 1/6/2010 10000
7 REP7 1/7/2010 18000-10000 = 8000
8 REP8 1/8/2010 15000
*/

select repcode, repname, tradedate, case when repcode=next_repcode then channel_total-next_total else channel_total end net_total from (
select rank() over(partition by repcode order by repcode, sort_order) rn, repcode, repname, tradedate, channel_total, 
    lead(repcode) over(order by repcode, sort_order) next_repcode,
    lead(channel_total) over(order by repcode, sort_order) next_total
from (
select 1 sort_order, t1.repcode,
	t1.repname,
	t1.tradedate,
	t1.channel_total
from tab1 t1
union all
select 2 sort_order, t2.repcode,
	t2.repname,
	t2.tradedate,
	t2.energy_total
from tab2 t2
)
)
where rn=1
/

Select allOpen in new window

This the result I get with that test:

REPCODE	REPNAME	TRADEDATE	NET_TOTAL
1	REP1	01-JAN-10	5000	
2	REP2	02-JAN-10	4000
3	REP3	03-JAN-10	1000
5	REP5	05-JAN-10	7000
6	REP6	06-JAN-10	10000
7	REP7	07-JAN-10	8000
8	REP8	08-JAN-10	15000

Select allOpen in new window

Thank you slightvw and others who have contributed to this.  To answer slightvw question(s).

1. For the dataset provided - for any repcode in dataset 1 there will be a matching repcode in dataset 2 and vice versa.  So you are correct my sample data should have had common reps. Thanks for clarifying it.

2.  Both Proc A and Proc B are inserting data into same table (though querying from different sources).

>>2.  Both Proc A and Proc B are inserting data into same table (though querying from different sources).

Then things might be much easier.  IT is likely you don't need the UINON ALL since the answer to the question appears to be coming from the same base table, we will need sample data and expected results from the base table instead of the output from both procedures.

Here are sample data and expected results from table.

SET 1 DATA:

REPNAME	       REPCODE	TRADE_DATE	SET1_TOTAL
FIRST CHOICE  	116	11/1/2016	3502.261243
FIRST CHOICE  	116	11/3/2016	3238.033006
FIRST CHOICE  	116	11/5/2016	2926.088051
GREEN STATES	118	11/1/2016	9347.013154
GREEN STATES	118	11/3/2016	8734.993027
GREEN STATES	118	11/5/2016	7855.813963
BLUE BELL	        120	11/6/2016	8130.656518
BLUE BELL	        120	11/8/2016	6353.693054
WHITE STAR	        122	11/10/2016	5000

SET 2:
REPNAME	       REPCODE	TRADE_DATE	SET2_TOTAL
FIRST CHOICE  	116	11/1/2016	1000
FIRST CHOICE  	116	11/3/2016	504.1
FIRST CHOICE  	116	11/5/2016	0
GREEN STATES	118	11/1/2016	500
GREEN STATES	118	11/3/2016	1005
GREEN STATES	118	11/5/2016	0
BLUE BELL	        120	11/6/2016	1000
BLUE BELL	        120	11/8/2016	2000
WHITE STAR	        122	11/10/2016	0


AFTER the MERGE data set and TOTALS 

REPNAME	       REPCODE	TRADE_DATE	SET1_TOTAL	SET2_TOTAL	NET_TOTAL
FIRST CHOICE 	116	11/1/2016	3502.261243	1000	        2502.261243
FIRST CHOICE 	116	11/3/2016	3238.133006	504.1	         2734.033 
FIRST CHOICE 	116	11/5/2016	2926.088051	500	       2426.088051
GREEN STATES	118	11/1/2016	9347.013154	500	        8847.013154
GREEN STATES	118	11/3/2016	8734.993027	1005	          7729.993027
GREEN STATES	118	11/5/2016	7855.813963	0	        7855.813963
BLUE BELL	        120	11/6/2016	8130.656518	1000	          7130.656518
BLUE BELL	        120	11/8/2016	6353.693054	2000	          4353.693054
WHITE STAR	        122	11/10/2016	5000	        0	            5000

Select allOpen in new window

Then it looks as simple as this:

select set1.REPNAME, set1.REPCODE, set1.TRADE_DATE, set1.SET1_TOTAL, set2.SET2_TOTAL, set1.SET1_TOTAL - set2.SET2_TOTAL "NET_TOTAL"
from set1, set2
where set2.REPNAME = set1.REPNAME
 and set2.REPCODE = set1.REPCODE
 and set1.TRADE_DATE = set1.TRADE_DATE;

Select allOpen in new window

This assumes that both tables have the complete set of matching records.  If that is not true, you will need an outer join on at least one of the tables, and an "nvl(...,0)" on the column from that table and the Net_total calculation.

It won't be a complete set of matched records. There will be records uncommon in each set. Will need a FULL OUTER JOIN as was suggested earlier by slightvw.

>>Here are sample data and expected results from table.

Not really what I'm looking for.  You said that  set1 and set2 data comes from the same table, I'm looking for data from that single table.  There is likely a column that specifies if the row in the table belongs in set1 or set2.  I'll need that column as well.

I'm looking for data in the SINGLE INPUT TABLE.

If you keep giving us two sets we have to write the SQL as if the data comes from TWO input tables.  The SQL I posted above should get you there but likely not the best way since it hits the same table at least twice.

The intervaldatasource value (l$CHANNEL or ENERGY )identifies the data belongs to Set 1 or Set 2.  There's also a database sequence column uidrec that is populated when records are inserted.

Here's the sample record set from the single table:

UIDREC	PARTITION_KEY	FIRSTDAY	LASTDAY	STARTTIME	STOPTIME	DUNSNUMBER	INTERVALDATASOURCE	ESIID	REPCODE	REPNAME	INTV1	INTV2	INTV3	INTV4	INTV5	TOTALS	SUBTRACTING_TOTAL	NET_TOTAL
100000001	20100101_20101231	10/1/2014	10/5/2014	10/2/2014	10/2/2014 23:59	799530915	L$CHANNEL	1008901023814480000000	121	RELIANCE	44	14	13	14	14	99	50	49
100000002	20100101_20101231	10/1/2014	10/5/2014	10/2/2014	10/2/2014 23:59	799530915	LSCHANNEL	1008901024900680000000	121	RELIANCE	216	217	217	217	216	1083	100	983
100000003	20100101_20101231	10/1/2014	10/5/2014	10/2/2014	10/2/2014 23:59	799530915	L$CHANNEL	1008901023900220000000	121	RELIANCE	115	114	116	112	112	569	100	469
100000004	20100101_20101231	10/1/2014	10/5/2014	10/2/2014	10/2/2014 23:59	799530915	ENERGY	10204049797541000	121	RELIANCE	12	11	12	12	11	58	50	8
100000005	20100101_20101231	10/1/2014	10/5/2014	10/2/2014	10/2/2014 23:59	799530915	ENERGY	1008901000180680000000	121	RELIANCE	7	7	6	7	6	33	10	23

Select allOpen in new window

>> There's also a database sequence column uidrec that is populated when records are inserted.

Do I need to care about this column as part of the solution?

>>Here's the sample record set from the single table:

What column is TRADEDATE?
What am I summing up to compute SET_TOTAL?

To save me a LOT of time, can you simplify that sample data to only include the columns I need?

Based on previous attempts, I think that is:
REPNAME
REPCODE
INTERVALDATASOURCE
TRADE_DATE
WHATEVER_NUMBER_TO_GENERATE_SET_TOTAL

I don't know what I use from your sample for the last two values.

The uidrec does not need to be factored in here.

TRADE_DATE = to_char(l.starttime,'MM/DD/YYYY')
NET_TOTAL = TOTALS - SUB_TOTAL

INTERVALDATASOURCE	TRADEDATE	REPCODE	REPNAME	          TOTALS	SUB_TOTAL	NET_TOTAL
L$CHANNEL	     10/2/2014	     121	 RELIANCE	    99	       50	           49
LSCHANNEL	     10/2/2014	     121	RELIANCE	    1083      100	          983
L$CHANNEL	     10/2/2014	     121	RELIANCE	    569       100	          469
ENERGY	    10/2/2014	     121       RELIANCE	            58         50	             8
ENERGY	     10/2/2014	     121       RELIANCE	            33         10	            23

Select allOpen in new window

>>NET_TOTAL = TOTALS - SUB_TOTAL

So is the SET1_TOTAL and SET2_TOTAL above a sum of NET_TOTAL?

Can you provide expected results based on those 5 rows?