Merge different data sets from same table using UNION

A stored procedure (inside a package) has data based on selection criteria

Proc A
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 , sum (totals) as "CHANNEL_TOTAL"       
            FROM REGION_EXTRACT WHERE l.intervaldatasource =  'CHANNEL'
           GROUP BY .......

Open in new window



Another procedure (in the same package), ProcB has dataset from a different source.

 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,
            SUM (TOTALS) AS ENERGY_TOTALS
            FROM REGION e  WHERE E.intervaldatasource =  'ENERGY'
            GROUP BY ......

Open in new window


I am in need of find a best way to merge the data sets  and perform a calculation by subtracting channel total  (1st set)from the energy total (2nd set_ to call it a net total?    Any pointers on how this can be done?

I've attempted to merge the 2 sets using UNION (see code below) but fails with "Invalid table name on the JOIN ON condition on Line 49.

           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 
           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 =  'LSCHANNEL'
            JOIN ON  (l.repcode = e.repcode)

Open in new window

steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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.
0
steve2312Author Commented:
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.

Open 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

Open in new window

0
Geert GOracle dbaCommented:
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
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mark GeerlingsDatabase AdministratorCommented:
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';

Open 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.
0
awking00Information Technology SpecialistCommented:
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.
0
slightwv (䄆 Netminder) Commented:
@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
/

Open 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

Open in new window

0
steve2312Author Commented:
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).
0
slightwv (䄆 Netminder) Commented:
>>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.
0
steve2312Author Commented:
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

Open in new window

0
Mark GeerlingsDatabase AdministratorCommented:
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;

Open 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.
0
steve2312Author Commented:
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.
0
slightwv (䄆 Netminder) Commented:
>>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.
0
steve2312Author Commented:
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

Open in new window

0
slightwv (䄆 Netminder) Commented:
>> 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.
0
steve2312Author Commented:
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

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>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?
0
slightwv (䄆 Netminder) Commented:
This is another complete guess.

If this is not correct, PLEASE, add or subtract from the test case I provided.  Please make sure you preserve the insert statements.

Do not post any more sample data that isn't in my test case.  The more you keep changing things, the long it will take to get you an answer.

Here is my setup:
/*
drop table tab1 purge;
create table tab1(INTERVALDATASOURCE varchar2(20), TRADEDATE date, REPCODE number, REPNAME varchar2(20), NET_TOTAL number);

insert into tab1 values('L$CHANNEL',to_date('10/2/2014','MM/DD/YYYY'),121,'RELIANCE',49);
insert into tab1 values('LSCHANNEL',to_date('10/2/2014','MM/DD/YYYY'),121,'RELIANCE',983);
insert into tab1 values('L$CHANNEL',to_date('10/2/2014','MM/DD/YYYY'),121,'RELIANCE',469);
insert into tab1 values('ENERGY',to_date('10/2/2014','MM/DD/YYYY'),121,'RELIANCE',8);
insert into tab1 values('ENERGY',to_date('10/2/2014','MM/DD/YYYY'),121,'RELIANCE',23);

--made up set for different repcode instead of 121
--only CHANNEL
insert into tab1 values('L$CHANNEL',to_date('10/2/2014','MM/DD/YYYY'),116,'MADE UP 1',100);
--only ENERGY
insert into tab1 values('ENERGY',to_date('10/2/2014','MM/DD/YYYY'),116,'MADE UP 2',100);

--ONE OF EACH
insert into tab1 values('L$CHANNEL',to_date('10/2/2014','MM/DD/YYYY'),116,'MADE UP 3',100);
insert into tab1 values('ENERGY',to_date('10/2/2014','MM/DD/YYYY'),116,'MADE UP 3',100);
commit;
*/

Open in new window


Here is my guess at the correct SQL:
select repname, repcode, tradedate, set1_total, set2_total, set1_total-set2_total net_total
from (
	select repname, repcode, to_char(tradedate,'MM/DD/YYYY') tradedate,
		nvl(sum(case when intervaldatasource='L$CHANNEL' then net_total end),0) set1_total,
		nvl(sum(case when intervaldatasource='ENERGY' then net_total end),0) set2_total
	from tab1
	group by repname, repcode, to_char(tradedate,'MM/DD/YYYY')
)
/
	

Open in new window


Here is the output I get putting it all together:
REPNAME                 REPCODE TRADEDATE  SET1_TOTAL SET2_TOTAL  NET_TOTAL
-------------------- ---------- ---------- ---------- ---------- ----------
RELIANCE                    121 10/02/2014        518         31        487
MADE UP 3                   116 10/02/2014        100        100          0
MADE UP 1                   116 10/02/2014        100          0        100
MADE UP 2                   116 10/02/2014          0        100       -100

Open in new window


If that still isn't correct, change my table values or add new insert statements in my provided test case.  After changing the provided test case, please provide the expected results on the new test case.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.