Join multiple table to one base table using the same Keys

Question

I'm currently writing an SQL query for a massive finance report out of Oracle. This information isn't necessary for the question but may provide some background: I'm essentially taking all gl_JEs from a specific timeframe and breaking these up into their actual transactions.

So I have 6 different subtables i've generated and a base table.

The base table contains two keys to generate a PK and some general information Header_ID and Line_Num

Each subtable record also contains these two keys and additional information. There can be a one to many relationship between the base table and a subtable. And a PK from the base can be in multiple subtables.

I want to join these 6 table together, preserving the general information from the base and adding the information from the subtables. I can do this with a Union ALL but it means I have to put the same fields on every subtable and I have to join LOTS of fields, so this is not optimal.

Here some example data if all that talking about did not make sense. I'm going to simplify it by only having 3 sub tables:

create table base (Header_id int,
                line_num int,
                period_num int);
create table subtable1 (header_id int,
                        line_num int,
                        cost    int,
                        description varchar(50));
create table subtable2 (header_id int,
                        line_num int,
                        invoice_num    int,
                        description varchar(50));
create table subtable3 (header_id int,
                        line_num int,
                        value    int,
                        description varchar(50));

Insert into base values
    (1, 1 , 4),
    (1,2,4),
    (2,5,4),
    (2,4,4),
    (3,1,4),
    (4,2,5);

Insert into subtable1 values
    (1, 1, 50, 'Boat'),
    (1, 1, 30, 'lifejacket'),
    (2, 4, 4000, 'lifeinsurance');
Insert into subtable2 values
    (1, 1, 89756, 'Boat supplies')
    (2,5, 5464, 'fake invoice');
Insert into subtable3 values
    (1, 2, 100, 'im running out')
    (3, 1, 25, 'of ideas');

I want to return something like this. Ignore my lack of nulls/field names please

 1. 1,1,4,50,'BOAT'
 2. 1,1,4,30,'lifejacket' 
 3. 1,1,4,89756, 'boat supplies'
 4. 1,2,4,100,'im running out'
 5. 2,5,4,5464,'fake invoice'
 6. 2,4,4,4000,'lifeinsurance'
 7. 3,1,4,25,'of ideas'
 8. 4,2,5

Any ideas?

EDIT: HEre is my actual Query as it stands now:

With 
base as 
    (
      SELECT 
           gjH.PERIOD_NAME Period
          ,gcc.SEGMENT1 || '-' || gcc.SEGMENT2 || '-' || gcc.SEGMENT3 || '-' || gcc.SEGMENT4 || '-' || gcc.SEGMENT5 || '-' || gcc.SEGMENT6 GL_COA
          ,gjL.JE_HEADER_ID
          ,gjL.JE_LINE_NUM
          ,gp.period_num
          ,gp.period_year
          ,gjh.date_created
          ,gjh.default_effective_date
          ,gjh.currency_code  base_currency_code
          ,gjh.currency_code  transaction_currency_code

      FROM
           apps.GL_JE_Batches         gjB
          ,apps.GL_JE_HEADERS         gjH
          ,apps.GL_JE_LINES           gjL
          ,apps.gl_code_combinations gcc
          ,apps.gl_periods            gp

      WHERE
              gjB.je_batch_id = gjH.JE_BATCH_ID
          and gjH.JE_HEADER_ID = gjL.JE_HEADER_ID
          and gjH.PERIOD_NAME in (:period, :period2, :period3)
          AND gjl.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID
          and (gjL.accounted_dr != 0 OR gjL.accounted_cr != 0)
          and gjH.Period_Name = gp.Period_Name
      )
,Subledger as -- Not all Base records have a Subledger record! 58825
    ( -- Return all the primary keys for the Subledger and then the FK to the different distribution tables, 
      SELECT
           base.period Period
          ,base.GL_COA GL_COA
          ,base.JE_Header_ID
          ,base.JE_Line_NUM
          ,base.period_num
          ,base.period_year
          ,base.date_created
          ,base.default_effective_date
          ,base.base_currency_code
          ,base.transaction_currency_code
          ,xal.GL_SL_LINK_TABLE
          ,xal.GL_SL_LINK_ID
          ,xal.ae_line_num
          ,xah.ae_header_id
          ,xah.application_id
          ,xe.event_id
          ,xte.entity_id
          ,xdl.SOURCE_DISTRIBUTION_TYPE     --Tells you what table to pull lines/info from
          ,xdl.SOURCE_DISTRIBUTION_ID_NUM_1 --Is the FK ID to the above distribution table.
          ,decode(nvl(xdl.unrounded_accounted_dr,0),0,xdl.unrounded_accounted_cr,-1*xdl.unrounded_accounted_dr) base_amount
          ,decode(nvl(xdl.unrounded_accounted_dr,0),0,xdl.unrounded_accounted_cr,-1*xdl.unrounded_accounted_dr) transaction_amount

      FROM
           base
          ,apps.gl_import_references             gRef
          ,xla.xla_ae_lines                      xal
          ,xla.xla_ae_headers                    xah
          ,xla.xla_events                        xe
          ,xla.xla_transaction_entities          xte
          ,xla.xla_distribution_links            xdl
      WHERE

              base.JE_HEADER_ID        = gRef.JE_HEADER_ID
          AND base.JE_LINE_NUM         = gRef.JE_LINE_NUM
          AND gRef.GL_SL_LINK_TABLE   = xal.GL_SL_LINK_TABLE
          AND gRef.GL_SL_LINK_ID      = xal.GL_SL_LINK_ID
          and xal.AE_HEADER_ID        = xah.ae_header_id
          and xal.APPLICATION_ID      = xah.APPLICATION_ID
          and xah.AE_HEADER_ID = xdl.AE_HEADER_ID
          and xal.AE_LINE_NUM = xdl.AE_LINE_NUM
          and xah.event_id = xe.event_id
          and xah.application_id = xe.application_id
          and xe.entity_id = xte.entity_id
          and xe.APPLICATION_ID = xte.APPLICATION_ID
      )

,AP_PMT_DIST as --5476
    (
      SELECT
        sl.period
        ,sl.period_num
        ,sl.period_year
        ,sl.gl_coa
        ,sl.JE_Header_ID
        ,sl.JE_line_num
        ,sl.base_amount base_amount
        ,sl.transaction_amount transaction_amount
        ,'AP_PMT_DIST' || '|' || apid.description  description
        ,sl.date_created
        ,sl.default_effective_date
        ,sl.base_currency_code
        ,sl.Transaction_currency_code

      FROM
         subledger                                      sl
        ,AP.AP_PAYMENT_HIST_DISTS                       aphd
        ,AP.AP_INVOICE_DISTRIBUTIONS_ALL                apid

      WHERE
            sl.SOURCE_DISTRIBUTION_TYPE = 'AP_PMT_DIST'
        AND sl.SOURCE_DISTRIBUTION_ID_NUM_1 = aphd.payment_hist_dist_id
        and apid.invoice_distribution_id = aphd.invoice_distribution_id
    )
,AP_INV_DIST as --4422
    (
      SELECT
         sl.period
        ,sl.period_num
        ,sl.period_year
        ,sl.gl_coa
        ,sl.JE_header_id
        ,sl.je_line_num
        ,sl.base_amount base_amount
        ,sl.transaction_amount transaction_amount
        ,'AP_INV_DIST' || '|' || apid.description description
          ,sl.date_created
          ,sl.default_effective_date
        ,sl.base_currency_code
        ,sl.Transaction_currency_code
      FROM
        subledger                         sl
        ,AP.AP_INVOICE_DISTRIBUTIONS_ALL apid
      WHERE
              sl.SOURCE_DISTRIBUTION_TYPE = 'AP_INV_DIST'
          AND sl.SOURCE_DISTRIBUTION_ID_NUM_1 = apid.INVOICE_DISTRIBUTION_ID 
    )  
,AP_PREPAY as --10 records
    (
      SELECT
         sl.period
        ,sl.period_num
        ,sl.period_year
        ,sl.gl_coa
        ,sl.JE_header_id
        ,sl.je_line_num
        ,sl.base_amount base_amount
        ,sl.transaction_amount transaction_amount
        ,'AP_PREPAY' || '|' || apid.description description
          ,sl.date_created
          ,sl.default_effective_date
        ,sl.base_currency_code
        ,sl.Transaction_currency_code

      FROM
        subledger                         sl
        ,AP.AP_PREPAY_APP_DISTS           appad
        ,AP.AP_Invoice_distributions_all  apid
      WHERE
                sl.SOURCE_DISTRIBUTION_TYPE = 'AP_PREPAY'
                AND sl.SOURCE_DISTRIBUTION_ID_NUM_1 = appad.PREPAY_APP_DIST_ID
                AND appad.Invoice_distribution_id = apid.invoice_distribution_id
    )
,AR_DIST as --34321 records
    (
      SELECT
         sl.period
        ,sl.period_num
        ,sl.period_year
        ,sl.gl_coa
        ,sl.JE_header_id
        ,sl.je_line_num
        ,sl.base_amount base_amount
        ,sl.transaction_amount transaction_amount
        ,'AR Invoice Line' description
          ,sl.date_created
          ,sl.default_effective_date
        ,sl.base_currency_code
        ,sl.Transaction_currency_code
      FROM
        subledger                         sl
        ,AR.AR_DISTRIBUTIONS_ALL          ARDA
      WHERE
              sl.SOURCE_DISTRIBUTION_TYPE = 'AR_DISTRIBUTIONS_ALL'
          and sl.SOURCE_DISTRIBUTION_ID_NUM_1 = ARDA.line_id
    )
,AR_CUST_TRX as --14596 records
    (
      SELECT
         sl.period
        ,sl.period_num 
        ,sl.period_year
        ,sl.gl_coa
        ,sl.JE_header_id
        ,sl.je_line_num
        ,sl.base_amount base_amount
        ,sl.transaction_amount transaction_amount              
        ,'Cust_Trx_Line' description
          ,sl.date_created
          ,sl.default_effective_date
          ,sl.base_currency_code
          ,sl.transaction_currency_code

      FROM
        subledger                            sl
        ,AR.RA_CUST_TRX_LINE_GL_DIST_ALL     ctld
        ,AR.RA_Customer_trx_lines_all       ctl
      WHERE
              sl.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
          AND sl.source_distribution_id_num_1 = ctld.cust_trx_line_gl_dist_id
          AND ctld.customer_trx_line_id  = ctl.customer_trx_line_id (+)
          --and ctld.amount <> ctl.revenue_amount
    )
,spreads as
    (
      SELECT 
           b.PERIOD Period
          ,b.period_num
          ,b.period_year
          ,b.GL_COA
          ,b.JE_HEADER_ID
          ,b.JE_LINE_NUM
          ,decode(nvl(gjl.accounted_dr,0),0,gjl.accounted_cr,-1*gjl.accounted_dr)  base_amount
          ,decode(nvl(gjl.accounted_dr,0),0,gjl.accounted_cr,-1*gjl.accounted_dr)  transaction_amount
          ,gjH.DESCRIPTION DESCRIPTION
          ,b.date_created
          ,b.default_effective_date
          ,b.base_currency_code
          ,b.transaction_currency_code         


      FROM
           base                       B
          ,apps.GL_JE_HEADERS         gjH
          ,apps.GL_JE_LINES           gjL

      WHERE
              B.JE_Header_ID = gjH.JE_Header_ID
          AND B.JE_Line_Num = gjL.JE_Line_num    
          and gjH.JE_HEADER_ID = gjL.JE_HEADER_ID
          and gjH.JE_CATEGORY = 'Adjustment'
          and gjH.JE_SOURCE = 'Spreadsheet'
    )    
-- okay now we have all the types! and the number of records line up from the subledger distribution list.  
-- Now we need to get the required information about each transaction.
-- We will need to pass: Base table linking information (JEHeader and JELine) and Information about the line.
-- Then we will join these all together.
--Okay I have a breakdown, lets first get the values and make sure it all lines up.
  --select * from ar_cust_trx;
  --select * from AP_INV_DIST;


  SELECT * FROM AP_INV_DIST   AID --4422
  UNION ALL
  Select * FROM AP_PMT_DIST   APD  --9898 5476
  Union ALL
  select * FROM AP_PREPAY     APR --9908 10
  Union ALL
  select * FROM AR_DIST       ARD --44229 34321
  Union ALL 
  select * FROM AR_CUST_TRX   ARC --58825  It got rid of duplicates within itself, despite the fact they arn't duplicates.
  Union ALL
  select * FROM spreads --60011 1186

It seems that in the same column (the last one) you want to pull values from different tables... and allow for nulls when the "smaller tables" don't have anything for a PK value. Can't think of anything other than UNION ALL between results of left outer joins. Or perhaps UNION ALL the smaller tables first and then just do one big outer join, if that is consistent with your actual requirement. — , Aug 26 '16 at 18:35
@mathguy I tried to say to ignore my nulls... what I really want is Header_ID, Line_Num, Cost, Invoice_num, Value, Description where some can be null — Cameron, Aug 26 '16 at 18:41
how about building the "LOTS of fields' into a temp table and rather than union all inserting each sub table and only the relevant columns in. Then selecting from temp table. During each insert you will only have to deal with relevant columns as long as your temp/staging table accepts nulls — Matt, Aug 26 '16 at 18:42
@matt unfortunately I only have read only access to this oracle database, so I don't believe I can create a temp table — Cameron, Aug 26 '16 at 18:43
Perhaps table variable(s) then? http://stackoverflow.com/questions/670461/does-oracle-have-an-equivalent-of-sql-servers-table-variables — Matt, Aug 26 '16 at 18:46
That might work... i don't know anything about it so I'll take a look. — Cameron, Aug 26 '16 at 18:56
so the sub tables have the same table structure some fields just have data others dot? Couldn't you union all the sub tables together and join once to the base table? Adding a hardcoded value to define the source table of a specific record? — xQbert, Aug 26 '16 at 19:10
unfortunately not @xQbert . The subtables all have different information and structure. My example is poor... sorry! — Cameron, Aug 26 '16 at 19:13
So is the pain point the initial build and refactoring or subsequent maintenance? (initial build can be solved using system table All_Tab_Cols and building the "Select statements" dynamically. Maintenance though I can't solve unless you want to use Dynamic SQl based on the table structure in All_tab_Cols. — xQbert, Aug 26 '16 at 19:16

score 0 · Accepted Answer · answered Aug 26 '16 at 18:57

0

Just use a UNION ALL - Something along these lines:

SELECT DISTINCT * FROM 
(
        SELECT 
           base.header_id
           , base.line_num
           , base.period_num
           , subtable1.cost col3
           , subtable1.description col4
        FROM
           base 
           left join subtable1 
           on base.header_id = subtable1.header_id and base.line_num = subtable1.line_num

    UNION ALL

        SELECT 
           base.header_id
           , base.line_num
           , base.period_num
           , subtable2.invoice_num
           , subtable2.description
        FROM
           base 
           left join subtable2
           on base.header_id = subtable2.header_id and base.line_num = subtable2.line_num

    UNION ALL

         ...
)

I just wrote this freehand, but something like that SHOULD do the trick, I believe.

Hope this helps!!

answered Aug 26 '16 at 18:57

John Bustos

19,036
17
89
151

That works if there are only 5 columns, but I need to return about 25, all of which pull from different places, might be null in some tables, etc This make adding any additional information to the query, or reordering things, a major pain as you have to update your select for every subtable every time you change anything. – Cameron Aug 26 '16 at 19:02
I hearya, @Cameron, I'm just not sure there's any better way, sadly... I'm afraid your query may pose the same issues. With this query, at least it's slightly compartmentalized and null columns are easily accounted for with a simple `, null` for that specific table's return value. – John Bustos Aug 26 '16 at 19:09
Again, I hope you get a better solution, but I'm afraid you may not... Keen to see what others propose.... – John Bustos Aug 26 '16 at 19:09

Join multiple table to one base table using the same Keys

1 Answers1