Outer Join with a Subquery problem

[Exie]

Hi,

I've got a long query which needs a difficult table that is structured like so:

UB,1,000123,1,"99973245"

... in order to get the correct row, I need to use a subselect and a crap conversion to get the number into a valid date format.

The problem is ... some records will not have an entry in CREC at all, so I need to make it an outer join.... but I cant because of the subselect.

I thought about compiling the subselect into a view ... but I need to pass a parameter into it. How else could I do this ?

Maybe I could try wrap this in a function... pass in the part number and the date... it could execute the SQL and return a result if there is one, or just null if there isnt <shrug>...

  and crec.crec1_co_site(+) = iLine.invs1_co_site
  and crec.crec1_rec_type(+) = 1
  and crec.crec1_ref(+) = iLine.invspart
  and crec.crec1_path(+) = 1
  and crec.crec1_date = (
      select min(z.crec1_date)
      from mtms.crec_data@PROD_DB z
      where z.crec1_co_site = crec.crec1_co_site
        and z.crec1_rec_type = crec.crec1_rec_type
        and z.crec1_ref = crec.crec1_ref
        and z.crec1_path = crec.crec1_path
        and to_date(to_char(((10000000 - z.crec1_date) - 2000000),'000000'),'YYMMDD') < iHead.invsdate_post
  );

 

[Dagon]

You could use an inline view. I think it would look something like this (which obviously I can't test because I don't have the full query, table scripts or any data).

select * from
iLine,
iHead,
(select *,
        to_date(to_char(((10000000 - z.crec1_date) - 2000000),'000000'),'YYMMDD') as converted_date
from 
mtms.crec_data@PROD_DB 
where crec.crec1_date = (
      select min(z.crec1_date)
      from mtms.crec_data@PROD_DB z
      where z.crec1_co_site = crec.crec1_co_site
        and z.crec1_rec_type = crec.crec1_rec_type
        and z.crec1_ref = crec.crec1_ref
        and z.crec1_path = crec.crec1_path)
where crec.crec1_co_site(+) = iLine.invs1_co_site
  and crec.crec1_rec_type(+) = 1
  and crec.crec1_ref(+) = iLine.invspart
  and crec.crec1_path(+) = 1
  and converted_date(+) < ihead.invsdate_post

The fact that you want the minimum date from CREC which is lower than ihead.invsdate_post makes things a bit easier. You can just take the minimum date of all records in the table and then compare that to ihead.invsdate_post. It might be a bit harder if you wanted maximum date which was less than ihead.invsdate_post.

Here's a little test script I used to mimic the problem:

drop table header;

drop table detail;

drop table extra_detail;

create table header (h number, headdate date, fkey number);

create table detail (h number, d varchar2(20));

create table extra_detail (fkey number, fdata varchar2(20), fdate varchar2(20));

insert into header values (1, trunc(sysdate), 1);
insert into header values (2, trunc(sysdate)-1, 2);
insert into header values (3, trunc(sysdate)-4, 3);

insert into detail values (1, 'Detail1.1');
insert into detail values (1, 'Detail1.2');
insert into detail values (1, 'Detail1.3');
insert into detail values (2, 'Detail2.1');
insert into detail values (3, 'Detail2.2');

insert into extra_detail values (1, 'Exdet 1', to_char(trunc(sysdate)+1, 'DD/MM/YYYY'));
insert into extra_detail values (1, 'Exdet 2', to_char(trunc(sysdate), 'DD/MM/YYYY'));
insert into extra_detail values (1, 'Exdet 3', to_char(trunc(sysdate)-1, 'DD/MM/YYYY'));
insert into extra_detail values (1, 'Exdet 4', to_char(trunc(sysdate)-2, 'DD/MM/YYYY'));

select h.h, h.headdate, d.d, ed.fdata
from header h, detail d,
extra_detail ed
where h.h = d.h
and ed.fkey(+) = h.fkey
and ed.FDATE (+) =
(select min(ed2.fdate)
from extra_detail ed2
where ed.fkey = ed2.fkey
and  ed2.fdate <= h.headdate)

select h.h, h.headdate, d.d, ed.fdata
from header h, 
     detail d,
    (select fkey, fdata, fdate
      from extra_detail ed
     where ed.FDATE =
       (select min(ed2.fdate)
         from extra_detail ed2
        where ed.fkey = ed2.fkey)) ed
where h.h = d.h
and ed.fkey(+) = h.fkey
and to_date(ed.FDATE (+), 'DD/MM/YYYY') <= h.headdate

 

[Dagon]

Ignore the second-last query there. It was an earlier effort that didn't work properly. The last one works correctly.

 

[Exie]

ho-hum... thanks Dagon

I see what your getting at.. my problem is my subquery needs to sumarize WITH a filter from another table... so theres sort of 3 tables involved...

table1 = table2 where (sumarized table1 = table3)

By making it an outer join ... what I'm aiming for is ...

table1 may or maynot = table2 where (sumarized table1 = table3)
(eg. product_cost = part_id where last cost at date_part_sold if the part actually has a cost record at all)

In business terms ... we sometimes sell products that we dont normally make eg. staplers .. as we dont make staplers, there will not be a cost record... but when we sell pillows which we do make, we want the cost record at the time we sold the pillow.

To use your example I'd need h.headdate to filter the summary of ed2... but ed2 is not exposed outside the inline view nor does it have access to "h".

Excuse the length... heres the full query:

SELECT 
        trim(cust.accm1_cus_sup)      as Customer_Id,
        trim(cust.accmname)           as Customer_Name,
        trim(cparent.accmname)        as Parent_Cust_Name,
        trim(cust.accmtax_code_2)     as cust_division,
        trim(cust.accmgroup)          as cust_group,
        docs.docsperiod         as Period, 
        iHead.invsdate_post     as Transaction_Date,
        trim(oHead.ordstax_code_2)    as branch_code,
        regexp_replace(branch.accbaddress3, '[0-9]|[-().]') as branch_desc,
        trim(cust.accmaddress_line4)  as State,
        substr(iHead.invssalesman,1,3) as sales_rep_id,
        trim(sman.empl3_surname)      as sales_rep_name,
        iHead.invsregion        as region,
        iHead.invs1_ref         as document_id,
        iLine.invs1_line        as document_line_id,
        trim(part.part1_part)         as Product_Id,
        trim(part.partdesc)           as product_description,
        trim(part.partprod_group)     as product_group,
        trim(part.partcommodity)      as commodity_code,
        trim(oLine.ords5_sal_buy)     as from_store,
        past.pastabc_class      as MTSFlag,
        iLine.invsqty           as Qty,
        iLine.invsUnit          as UOM,
        iLine.invsNet_Value     as total_value,
        crec.crectotal_cost     as item_cost,
        TO_CHAR (iHead.invsdate_post, 'YYYY') AS transaction_year,
        TO_CHAR (iHead.invsdate_post, 'MM') AS transaction_month,
        TO_CHAR (iHead.invsdate_post, 'DD') AS transaction_day,
        'Q' || TO_CHAR (iHead.invsdate_post, 'Q') AS transaction_quarter,
        (iLine.invsqty * crec.crectotal_cost) as total_cost,
        trim(cust.accm3_pr_cus_sup)   as parent_id,
        trim(cust.accmsal_buy)        as acctmgr_id,
        trim(amgr.empl3_surname)      as acctmgr
from mtms.invs_data@PROD_DB iHead,
     mtms.invs_data@PROD_DB iLine,
     mtms.accm_data@PROD_DB cust,
     mtms.accm_data@PROD_DB cparent,
     mtms.accb_data@PROD_DB branch,
     mtms.docs_data@PROD_DB docs,
     mtms.ords_data@PROD_DB oHead,
     mtms.ords_data@PROD_DB oLine,
     mtms.part_data@PROD_DB part,
     mtms.past_data@PROD_DB past,
     mtms.crec_data@PROD_DB crec,
     mtms.empl_data@PROD_DB sman,
     mtms.empl_data@PROD_DB amgr
where iHead.invs3_co_site ='UB'
  and iHead.invs3_line = 0
  and iHead.invs3_status = 6
  and iHead.invsdate_post between '01JUL06' and '31DEC10'
------------------------
  and iLine.invs1_co_site = iHead.invs1_co_site
  and iLine.invs1_rec_type = iHead.invs1_rec_type
  and iLine.invs1_ref = iHead.invs1_ref
  and iLine.invs1_line > 0
-------------------------  
  and cust.accm1_co_site = iHead.invs1_co_site
  and cust.accm1_rec_type = 1
  and cust.accm1_cus_sup = iHead.invs4_cus_sup
-------------------------  
  and cparent.accm1_co_site(+) = cust.accm1_co_site
  and cparent.accm1_rec_type(+) = 1
  and cparent.accm1_cus_sup(+) = cust.accm3_pr_cus_sup 
-------------------------
  and branch.accb1_co_site = cust.accm1_co_site
  and branch.accb1_rec_type = cust.accm1_rec_type
  and branch.accb1_rec_ref = cust.accm1_cus_sup
  and branch.accb1_rec_line = 0
  and branch.accb1_ref = oHead.ordstax_code_2
-------------------------
  and docs.docs3_co_site = iHead.invs1_co_site
  and docs.docs3_rec_type = 1
  and docs.docs3_document = iHead.invs1_ref
-------------------------
  and oHead.ords1_co_site = iLine.invs2_co_site
  and oHead.ords1_rec_type = 50
  and oHead.ords1_ref = iLine.invs2_order
  and oHead.ords1_line = 0
-------------------------
  and oLine.ords1_co_site = iLine.invs2_co_site 
  and oLine.ords1_rec_type = 50
  and oLine.ords1_ref = iLine.invs2_order
  and oLine.ords1_line = iLine.invs2_ord_line
-------------------------
  and part.part1_co_site(+) = iLine.invs1_co_site
  and part.part1_rec_type(+) = 1
  and part.part1_part(+) = iLine.invspart
-------------------------
  and past.past1_co_site(+) = oLine.ords5_co_site
  and past.past1_rec_type(+) = 5
  and past.past1_part(+) = oLine.ords3_part
  and past.past1_store(+) = oLine.ords5_sal_buy
-------------------------
  and sman.empl1_co_site(+) = iHead.invs1_co_site
  and sman.empl1_rec_type(+) = 1
  and sman.empl1_code(+) = rpad(substr(iHead.invssalesman,1,3),6)
-------------------------
  and amgr.empl1_co_site(+) = cust.accm1_co_site 
  and amgr.empl1_rec_type(+) = cust.accm1_rec_type
  and amgr.empl1_code(+) = cust.accmsal_buy
-------------------------
  and crec.crec1_co_site = iLine.invs1_co_site
  and crec.crec1_rec_type = 1
  and crec.crec1_ref = iLine.invspart
  and crec.crec1_path = 1
  and crec.crec1_date = (
      select min(z.crec1_date)
      from mtms.crec_data@PROD_DB z
      where z.crec1_co_site = crec.crec1_co_site
        and z.crec1_rec_type = crec.crec1_rec_type
        and z.crec1_ref = crec.crec1_ref
        and z.crec1_path = crec.crec1_path
        and to_date(to_char(((10000000 - z.crec1_date) - 2000000),'000000'),'YYMMDD') < iHead.invsdate_post
  );

PS. The reason I'm using a min() and the crazy to_date function is crec1_date is an integer field that holds a serial date number.

 

[Dagon]

As far as I can see, the main difference between your query and mine is that you are outer-joining to iLine but the date condition comes from iHead. This does lead to a problem of trying to outer-join to two different tables, but it should be possible to get round it by joining iLine to Crec first.

Going back to my simplified example, I came up with this:

drop table header;

drop table detail;

drop table extra_detail;

create table header (hline number, headdate date);

create table detail (hline number, dline number, ddata varchar2(20), ekey number);

create table extra_detail (ekey number, edata varchar2(20), edate varchar2(20));

insert into header values (1, trunc(sysdate));
insert into header values (2, trunc(sysdate)-1);
insert into header values (3, trunc(sysdate)-4);

insert into detail values (1, 1, 'Detail1.1', 1);
insert into detail values (1, 2, 'Detail1.2', 2);
insert into detail values (1, 3, 'Detail1.3', 3);
insert into detail values (2, 4, 'Detail2.1', 4);
insert into detail values (3, 5, 'Detail2.2', 5);

insert into extra_detail values (1, 'Exdet 1.1', to_char(trunc(sysdate)+1, 'YYYY/MM/DD'));
insert into extra_detail values (1, 'Exdet 1.2', to_char(trunc(sysdate), 'YYYY/MM/DD'));
insert into extra_detail values (1, 'Exdet 1.3', to_char(trunc(sysdate)-1, 'YYYY/MM/DD'));
insert into extra_detail values (1, 'Exdet 1.4', to_char(trunc(sysdate)-2, 'YYYY/MM/DD'));
insert into extra_detail values (2, 'Exdet 2.1', to_char(trunc(sysdate)-7, 'YYYY/MM/DD'));
insert into extra_detail values (4, 'Exdet 4.1', to_char(trunc(sysdate)+5, 'YYYY/MM/DD'));

select h.hline, c.dline, c.ekey, h.headdate, c.ddata,  case when c.FDATE <= h.headdate then c.edata end
from header h, 
    (select d.dline,
            d.ddata,
            d.ekey,
            d.hline,
            ed.edata,
            to_date(ed.edate, 'YYYY/MM/DD') as fdate
     from detail d,
    (select ekey, edata, edate
      from extra_detail ed
     where ed.edate =
       (select min(ed2.edate)
         from extra_detail ed2
        where ed.ekey = ed2.ekey)) ed
     where ed.ekey(+) = d.ekey) c
where h.hline = c.hline