# Thread: Sum of two table values and values from both the tables

1. Registered User
Join Date
May 2010
Posts
56

## Unanswered: Sum of two table values and values from both the tables

Table1 has
A|b|c|Val_Date as 4 columns
Table2 has
A|b|c|Val_Date as 4 columns

I want :
sum(Table1 .a+Table2.a),
sum((Table1 .b+Table2.b),
sum((Table1 .c+Table2.c),
val_date
from both tables where :
val_date matches
Union
And otherwise a,b,c,val_date values from Table1 where the val_date is not in Table2
Union
a,b,c,val_date values from Table2 where the val_date is not in Table1

Thanks..

2. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Perhaps something like...
(Not tested.)
Code:
```SELECT SUM( NVL(t1.a , 0) + NVL(t2.a , 0) ) AS sum_a
, SUM( NVL(t1.b , 0) + NVL(t2.b , 0) ) AS sum_b
, SUM( NVL(t1.c , 0) + NVL(t2.c , 0) ) AS sum_c
, NVL(t1.val_date , t2.val_date)       AS val_date
FROM  table1 t1
FULL  OUTER JOIN
table2 t2
ON   t1.val_date = t2.val_date
GROUP BY
NVL(t1.val_date , t2.val_date)```

3. Registered User
Join Date
May 2010
Posts
56
Thanks for the solution tonkuma.
Outer Join doesn't work but following works..

selectsum(Table1.a+Table2.a),sum((Table1.b+Table2. b),sum((Table1.c+Table2.c),
table1.val_date from table1 t1, table1 t2wheret1.val_date =t2.val_date
union
select * from table1 t
where not exists ( select null from table2
where val_date =t.val_date)
union
select * from table2 tt
where not exists (
select null from table1 where val_date =tt.val_date)

But causes a lot of expensive scans on table2 which is having hundreds of millions of rows..
Can this be done using Case or some other way, so that only a single scan is done on table2.

Thanks..

4. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Outer Join doesn't work but following works..
What did you mean?
Syntax error? or Incorrect out(different from expected)?

5. Registered User
Join Date
May 2010
Posts
56
Hi Tonkuma,

It's the incorrect Output..

Thanks..

6. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>It's the incorrect Output..

how do we know what is the correct/desired output?

7. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
Please publish test data(CREATE TABLE statements and INSERT statements)
and your executed SELECT statement and results,
and your expected results.

So, I might be able to revise my query to satisfy your requirements.

8. Registered User
Join Date
May 2010
Posts
56
Hi tonkuma,

Thanks for all the replies.

My exact requirement is as follows with table structures and data:

CREATE TABLE EOD_Table
(
PSTNG_FCT_ID NUMBER(15) NOT NULL,
ACNTNG_ENTRY_ID VARCHAR2(255 BYTE) NOT NULL,
ACNTNG_ENTRY_SRC VARCHAR2(255 BYTE) NOT NULL,
TXN_ID VARCHAR2(255 BYTE) NOT NULL,
REF_ID VARCHAR2(255 BYTE),
TRD_ID VARCHAR2(255 BYTE),
ACNTNG_ENTRY_TYP NUMBER(1) NOT NULL,
HRARCHY_DMN_ID NUMBER(15) NOT NULL,
PSTNG_CRNCY_ID NUMBER(15) NOT NULL,
PNL_TYP_DMN_ID NUMBER(15),
ACNT_DMN_ID NUMBER(15) NOT NULL,
PRDUCT_DMN_ID NUMBER(15) NOT NULL,
SCRTY_ID VARCHAR2(255 BYTE),
GL_ACTVTY_TYP VARCHAR2(255 BYTE),
ACNTNG_EVENT VARCHAR2(255 BYTE) NOT NULL,
SRC_SYSTEM_DT DATE NOT NULL,
VAL_DT_DMN_ID NUMBER(15) NOT NULL,
REVSN_DT TIMESTAMP(6) NOT NULL,
TRNST VARCHAR2(255 BYTE),
PSTNG_TYP VARCHAR2(255 BYTE) NOT NULL,
PSTNG_AMNT NUMBER(20,5) NOT NULL,
PSTD_ON_GL NUMBER(1) DEFAULT 0,
PSTD_DT DATE,
PSTNG_RMRKS VARCHAR2(255 BYTE),
CREATED_BY VARCHAR2(50 BYTE) NOT NULL,
CREATION_DT TIMESTAMP(6) DEFAULT SYSDATE,
MODIFIED_BY VARCHAR2(50 BYTE),
MODIFICATION_DT TIMESTAMP(6) DEFAULT SYSDATE
);

CREATE TABLE FactTable
(
HRARCHY_DMN_ID NUMBER(15) NOT NULL,
PRDUCT_DMN_ID NUMBER(15) NOT NULL,
TXN_CRNCY_ID NUMBER(15) NOT NULL,
ACNT_DMN_ID NUMBER(15) NOT NULL,
TXN_ID VARCHAR2(255 BYTE),
TXN_SRC VARCHAR2(255 BYTE) NOT NULL,
ACNTNG_ENTRY_TYP NUMBER(1) NOT NULL,
VAL_DT_DMN_ID NUMBER(15) NOT NULL,
REVSN_DT DATE NOT NULL,
CRNT_BLNC NUMBER(20,5) NOT NULL,
MTD_AVRG NUMBER(20,5) NOT NULL,
YTD_AVRG NUMBER(20,5) NOT NULL,
CRTD_BY VARCHAR2(50 BYTE) NOT NULL,
CRTN_DT TIMESTAMP(6) NOT NULL,
MDFD_BY VARCHAR2(50 BYTE),
MDFCTN_DT TIMESTAMP(6)
);

Insert into EOD_Table
(PSTNG_FCT_ID, ACNTNG_ENTRY_ID, ACNTNG_ENTRY_SRC, TXN_ID, TRD_ID, ACNTNG_ENTRY_TYP, HRARCHY_DMN_ID, PSTNG_CRNCY_ID,

PNL_TYP_DMN_ID, ACNT_DMN_ID, PRDUCT_DMN_ID, SCRTY_ID, GL_ACTVTY_TYP, ACNTNG_EVENT, SRC_SYSTEM_DT, VAL_DT_DMN_ID, REVSN_DT, TRNST,

PSTNG_TYP, PSTNG_AMNT, PSTD_ON_GL, PSTD_DT, CREATED_BY, CREATION_DT, MODIFIED_BY, MODIFICATION_DT)
Values
(1360, 'AE1', 'K2', 'C20903',
'C20903', 0, 6, 153, -1,
58, 19, 'SID1', 'AT1', 'MONTH END',
TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2192, TO_TIMESTAMP('11/1/2010 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY

fmHH12fm:MI:SS.FF AM'), '48082', 'Credit',
200, 2, TO_DATE('03/13/2012 16:55:25', 'MM/DD/YYYY HH24:MI:SS'), 'File InputProcessor',
TO_TIMESTAMP('3/21/2012 10:43:04.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'ExportToGL', TO_TIMESTAMP('3/21/2012

10:43:04.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into EOD_Table
(PSTNG_FCT_ID, ACNTNG_ENTRY_ID, ACNTNG_ENTRY_SRC, TXN_ID, TRD_ID, ACNTNG_ENTRY_TYP, HRARCHY_DMN_ID, PSTNG_CRNCY_ID,

PNL_TYP_DMN_ID, ACNT_DMN_ID, PRDUCT_DMN_ID, SCRTY_ID, GL_ACTVTY_TYP, ACNTNG_EVENT, SRC_SYSTEM_DT, VAL_DT_DMN_ID, REVSN_DT, TRNST,

PSTNG_TYP, PSTNG_AMNT, PSTD_ON_GL, PSTD_DT, CREATED_BY, CREATION_DT, MODIFIED_BY, MODIFICATION_DT)
Values
(1360, 'AE1', 'K2', 'C20903',
'C20903', 0, 6, 153, -1,
58, 19, 'SID1', 'AT1', 'MONTH END',
TO_DATE('11/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2193, TO_TIMESTAMP('11/2/2010 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY

fmHH12fm:MI:SS.FF AM'), '48082', 'Debit',
100, 2, TO_DATE('03/13/2012 16:55:25', 'MM/DD/YYYY HH24:MI:SS'), 'File InputProcessor',
TO_TIMESTAMP('3/21/2012 10:43:04.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'ExportToGL', TO_TIMESTAMP('3/21/2012

10:43:04.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into EOD_Table
(PSTNG_FCT_ID, ACNTNG_ENTRY_ID, ACNTNG_ENTRY_SRC, TXN_ID, TRD_ID, ACNTNG_ENTRY_TYP, HRARCHY_DMN_ID, PSTNG_CRNCY_ID,

PNL_TYP_DMN_ID, ACNT_DMN_ID, PRDUCT_DMN_ID, SCRTY_ID, GL_ACTVTY_TYP, ACNTNG_EVENT, SRC_SYSTEM_DT, VAL_DT_DMN_ID, REVSN_DT, TRNST,

PSTNG_TYP, PSTNG_AMNT, PSTD_ON_GL, PSTD_DT, CREATED_BY, CREATION_DT, MODIFIED_BY, MODIFICATION_DT)
Values
(1360, 'AE1', 'K2', 'C20903',
'C20903', 0, 6, 153, -1,
58, 19, 'SID1', 'AT1', 'MONTH END',
TO_DATE('10/28/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2188, TO_TIMESTAMP('10/28/2010 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY

fmHH12fm:MI:SS.FF AM'), '48082', 'Credit',
200, 2, TO_DATE('10/28/2012 16:55:25', 'MM/DD/YYYY HH24:MI:SS'), 'File InputProcessor',
TO_TIMESTAMP('3/21/2012 6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'ExportToGL', TO_TIMESTAMP('3/21/2012

6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into EOD_Table
(PSTNG_FCT_ID, ACNTNG_ENTRY_ID, ACNTNG_ENTRY_SRC, TXN_ID, TRD_ID, ACNTNG_ENTRY_TYP, HRARCHY_DMN_ID, PSTNG_CRNCY_ID,

PNL_TYP_DMN_ID, ACNT_DMN_ID, PRDUCT_DMN_ID, SCRTY_ID, GL_ACTVTY_TYP, ACNTNG_EVENT, SRC_SYSTEM_DT, VAL_DT_DMN_ID, REVSN_DT, TRNST,

PSTNG_TYP, PSTNG_AMNT, PSTD_ON_GL, PSTD_DT, CREATED_BY, CREATION_DT, MODIFIED_BY, MODIFICATION_DT)
Values
(1360, 'AE1', 'K2', 'C20903',
'C20903', 0, 6, 153, -1,
58, 19, 'SID1', 'AT1', 'MONTH END',
TO_DATE('10/28/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2185, TO_TIMESTAMP('10/25/2010 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY

fmHH12fm:MI:SS.FF AM'), '48082', 'Credit',
200, 2, TO_DATE('10/28/2012 16:55:25', 'MM/DD/YYYY HH24:MI:SS'), 'File InputProcessor',
TO_TIMESTAMP('3/21/2012 6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'ExportToGL', TO_TIMESTAMP('3/21/2012

6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into EOD_Table
(PSTNG_FCT_ID, ACNTNG_ENTRY_ID, ACNTNG_ENTRY_SRC, TXN_ID, TRD_ID, ACNTNG_ENTRY_TYP, HRARCHY_DMN_ID, PSTNG_CRNCY_ID,

PNL_TYP_DMN_ID, ACNT_DMN_ID, PRDUCT_DMN_ID, SCRTY_ID, GL_ACTVTY_TYP, ACNTNG_EVENT, SRC_SYSTEM_DT, VAL_DT_DMN_ID, REVSN_DT, TRNST,

PSTNG_TYP, PSTNG_AMNT, PSTD_ON_GL, PSTD_DT, CREATED_BY, CREATION_DT, MODIFIED_BY, MODIFICATION_DT)
Values
(1361, 'AE1', 'K2', 'C20908',
'C20908', 0, 6, 153, -1,
58, 19, 'SID1', 'AT1', 'MONTH END',
TO_DATE('10/28/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2185, TO_TIMESTAMP('10/25/2010 12:00:00.000000 AM','fmMMfm/fmDDfm/YYYY

fmHH12fm:MI:SS.FF AM'), '48082', 'Credit',
200, 2, TO_DATE('10/28/2012 16:55:25', 'MM/DD/YYYY HH24:MI:SS'), 'File InputProcessor',
TO_TIMESTAMP('3/21/2012 6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'ExportToGL', TO_TIMESTAMP('3/21/2012

6:16:39.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));

-----------------------------------

9. Registered User
Join Date
May 2010
Posts
56
SET DEFINE OFF;
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2192, TO_DATE('11/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 600,
200, 200, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2193, TO_DATE('11/02/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 500,
50, 50, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2185, TO_DATE('10/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200,
11.76, 0.79, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2186, TO_DATE('10/26/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200,
11.11, 0.78, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2187, TO_DATE('10/27/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200,
10.53, 0.78, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2188, TO_DATE('10/28/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 400,
20, 1.56, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into FactTable
(HRARCHY_DMN_ID, PRDUCT_DMN_ID, TXN_CRNCY_ID, ACNT_DMN_ID, TXN_ID, TXN_SRC, ACNTNG_ENTRY_TYP, VAL_DT_DMN_ID, REVSN_DT, CRNT_BLNC, MTD_AVRG, YTD_AVRG, CRTD_BY, CRTN_DT, MDFD_BY, MDFCTN_DT)
Values
(6, 19, 153, 58, 'C20903',
'K2', 0, 2189, TO_DATE('10/29/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 400,
19.05, 1.55, 'EOD TB ETL', TO_TIMESTAMP('3/30/2012 6:49:21.336799 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'), 'Anonymous',
TO_TIMESTAMP('3/30/2012 6:49:21.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

Output required is

1)Sums of crnt_blnc, MTD_Avrg, YTD_Avrg from both tables EOD_Table e AND FactTable f when :

f.hrarchy_dmn_id = e.hrarchy_dmn_id
AND f.prduct_dmn_id = e.prduct_dmn_id
AND f.txn_crncy_id = e.crncy_dmn_id
AND f.acnt_dmn_id = e.acnt_dmn_id
AND f.txn_id = e.txn_id
AND f.txn_src = e.txn_src
AND f.acntng_entry_typ = e.acntng_entry_typ
AND f.val_dt_dmn_id = e.val_dt_dmn_id

2) And crnt_blnc, MTD_Avrg, YTD_Avrg from EOD_Table e when these values don't match from values in FactTable f

3) And crnt_blnc, MTD_Avrg, YTD_Avrg from FactTable f when these values don't match from values in EOD_Table e

Using my query with 3 different queries for above 3 reqs. and Unioning them I get the correct Output which is:

14 rows as :
HRARCHY_DMN_ID,PRDUCT_DMN_ID,CRNCY_DMN_ID,ACNT_DMN _ID,TXN_ID,TXN_SRC,ACNTNG_ENTRY_TYP,VAL_DT_DMN_ID, REVSN_DT,CRNT_BLNC,MTD_AVRG,YTD_AVRG,CRTD_BY,CRTN_ DT,MDFD_BY,MDFCTN_DT
6,19,153,58,C20903,K2,0,2185,10/25/2010 12:00:00.000000 AM,200,11.7647058823529,0.78740157480315,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2186,10/26/2010 12:00:00.000000 AM,200,11.1111111111111,0.784313725490196,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2187,10/27/2010 12:00:00.000000 AM,200,10.5263157894737,0.78125,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2188,10/28/2010 12:00:00.000000 AM,400,20,1.55642023346304,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2189,10/29/2010 12:00:00.000000 AM,400,19.047619047619,1.55038759689922,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2192,11/1/2010 12:00:00.000000 AM,600,200,200,EOD TB ETL,3/31/2012 8:14:21.226732 PM
+05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20903,K2,0,2193,11/2/2010 12:00:00.000000 AM,500,50,50,EOD TB ETL,3/31/2012 8:14:21.226732 PM
+05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2185,10/25/2010 12:00:00.000000 AM,200,11.7647058823529,0.78740157480315,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2186,10/26/2010 12:00:00.000000 AM,200,11.1111111111111,0.784313725490196,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2187,10/27/2010 12:00:00.000000 AM,200,10.5263157894737,0.78125,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2188,10/28/2010 12:00:00.000000 AM,200,10,0.778210116731518,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2189,10/29/2010 12:00:00.000000 AM,200,9.52380952380952,0.775193798449612,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2192,11/1/2010 12:00:00.000000 AM,200,0,0,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

6,19,153,58,C20908,K2,0,2193,11/2/2010 12:00:00.000000 AM,200,0,0,EOD TB ETL,3/31/2012 8:14:21.226732 PM +05:30,Anonymous,3/31/2012 8:14:21 PM

But I am thinking of a better way to do this using Case or Outer Join or may be something else..

Thanks

10. Registered User
Join Date
Nov 2003
Posts
2,993

Especially the part about code tags

11. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
post FORMATTED EXPLAIN PLAN for working solution

https://forums.oracle.com/forums/thr...46101#10246101
Last edited by anacedent; 03-31-12 at 14:22.

12. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
...
...

Output required is

1)Sums of crnt_blnc, MTD_Avrg, YTD_Avrg from both tables EOD_Table e AND FactTable f when :

f.hrarchy_dmn_id = e.hrarchy_dmn_id
AND f.prduct_dmn_id = e.prduct_dmn_id
AND f.txn_crncy_id = e.crncy_dmn_id
AND f.acnt_dmn_id = e.acnt_dmn_id
AND f.txn_id = e.txn_id
AND f.txn_src = e.txn_src
AND f.acntng_entry_typ = e.acntng_entry_typ
AND f.val_dt_dmn_id = e.val_dt_dmn_id

2) And crnt_blnc, MTD_Avrg, YTD_Avrg from EOD_Table e when these values don't match from values in FactTable f

3) And crnt_blnc, MTD_Avrg, YTD_Avrg from FactTable f when these values don't match from values in EOD_Table e

Using my query with 3 different queries for above 3 reqs. and Unioning them I get the correct Output which is:

14 rows as :
...
...
What results did you got from my query example using FULL OUTER JOIN?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•