Re: Outer Join (?) Problem

From: Angela Morgan <agmorgan_at_my-deja.com>
Date: Thu, 31 Aug 2000 15:24:50 GMT
Message-ID: <8oltb8$ldi$1@nnrp1.deja.com>

In article <8okco5$u1c$1_at_nnrp1.deja.com>,   pollachi_at_my-deja.com wrote:
> Try this :
>
> select e.org_id, e.ec_type, e.ec_dol_mth, e.ec_dol_ytd,
> b.ec_tot_dol_mth, b.ec_tot_dol_ytd
> from eom_ec_data e, btm_ec_data b
> where e.fy_yr = 1999
> and e.data_dt = '31-OCT-99'
> and e.org_id = b.org_id (+)
> and e.ec_type = b.ec_type (+)
> and b.model_id(+) = 24
> union
> select b.org_id, b.ec_type, e.ec_dol_mth, e.ec_dol_ytd,
> b.ec_tot_dol_mth, b.ec_tot_dol_ytd
> from eom_ec_data e, btm_ec_data b
> where e.fy_yr(+) = 1999
> and e.data_dt(+) = '31-OCT-99'
> and e.org_id (+) = b.org_id
> and e.ec_type (+) = b.ec_type
> and b.model_id(+) = 24
>
> HTH
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Hi,

I really do appreciate your reply! I tried your SQL but, unfortunately, had the same problem with it that I have been having with my previous attempts at a solution. (I am running against our full data set. In trying to simplify the situation in my original post, I may have inadvertently omitted some important information.) In any case, after sleeping on it I came up with the following approach, using "nested" temp tables. It's pretty ugly, but it utilizes the indexes, runs quickly, and returns the right data.

If you know of a more elegant way to accomplish this, please post it.

Thanks!

--angela

SELECT
   eom.org_id,
   eom.lgcy_rpt_sym,
   sys_ec_type_tbl.ec_desc,

   NVL(eom.ec_dol_mth,0) actual_mtd,
   NVL(eom.ec_dol_ytd,0) actual_ytd,
   NVL(btm.ec_tot_dol_mth,0) target_mtd,
   NVL(btm.ec_tot_dol_ytd,0) target_ytd,

   ROUND(NVL(eom.ec_dol_mth,0)) - ROUND(NVL(btm.ec_tot_dol_mth,0)) variance_mtd,

   ROUND(NVL(eom.ec_dol_ytd,0)) - ROUND(NVL(btm.ec_tot_dol_ytd,0)) variance_ytd
FROM
   (SELECT

      ec_temp.org_id,
      ec_temp.lgcy_rpt_sym,
      ec_temp.ec_type,
      eom_ec_data.ec_dol_mth,
      eom_ec_data.ec_dol_ytd
   FROM
      (SELECT
         org_id,
         lgcy_rpt_sym,
         ec_type
      FROM
         btm_ec_data
      WHERE
         model_id = 24 AND
         (org_id = 173 OR ru_org_id = 173) AND
         to_char(data_dt,'MMYYYY') = '101998' AND
         ec_type LIKE ('61%')
      UNION
      SELECT
         org_id,
         lgcy_rpt_sym,
         ec_type
      FROM
         eom_ec_data
      WHERE
         (org_id = 173 OR ru_org_id = 173) AND
         to_char(data_dt,'MMYYYY') = '101998' AND
         ec_type LIKE ('61%')) ec_temp,
      eom_ec_data
   WHERE
      eom_ec_data.org_id (+) = ec_temp.org_id AND
      eom_ec_data.ec_type (+) = ec_temp.ec_type AND
      to_char(eom_ec_data.data_dt (+), 'MMYYYY') = '101998') eom,
   (SELECT
      ec_temp.org_id,
      ec_temp.lgcy_rpt_sym,
      ec_temp.ec_type,
      btm_ec_data.data_dt,
      btm_ec_data.ec_tot_dol_mth,
      btm_ec_data.ec_tot_dol_ytd
   FROM
      (SELECT
         org_id,
         lgcy_rpt_sym,
         ec_type
      FROM
         btm_ec_data
      WHERE
         model_id = 24 AND
         (org_id = 173 OR ru_org_id = 173) AND
         to_char(data_dt,'MMYYYY') = '101998' AND
         ec_type LIKE ('61%')
      UNION
      SELECT
         org_id,
         lgcy_rpt_sym,
         ec_type
      FROM
         eom_ec_data
      WHERE
         (org_id = 173 OR ru_org_id = 173) AND
         to_char(data_dt,'MMYYYY') = '101998' AND
         ec_type LIKE ('61%')) ec_temp,
      btm_ec_data
   WHERE
      btm_ec_data.model_id (+) = 24 AND
      btm_ec_data.org_id (+) = ec_temp.org_id AND
      btm_ec_data.ec_type (+) = ec_temp.ec_type AND
      to_char(btm_ec_data.data_dt (+), 'MMYYYY') = '101998') btm,

   sys_ec_type_tbl
WHERE

   eom.org_id = btm.org_id AND
   eom.ec_type = btm.ec_type AND
   eom.ec_type = sys_ec_type_tbl.ec_type

ORDER BY
   eom.lgcy_rpt_sym,
   eom.ec_type;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Aug 31 2000 - 10:24:50 CDT