Results 1 to 12 of 12
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    Join Date
    May 2010
    Posts
    56
    Hi Tonkuma,

    It's the incorrect Output..

    Thanks..

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It's the incorrect Output..

    how do we know what is the correct/desired output?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    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. #8
    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. #9
    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. #10
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Please read this: http://www.dbforums.com/oracle/10316...s-posters.html

    Especially the part about code tags

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    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
  •