| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Outer Join (?) Problem
Hello,
I am having trouble getting the results I need from this query. I hope someone can help me!
I have two tables, eom_ec_data and btm_ec_data, with pertinent fields as follows:
EOM_EC_DATA:
fy_yr number(4)
org_id number(5)
ec_type varchar2(4) [foreign key references sys_ec_type_tbl.ec_type]
data_dt date
ec_dol_mth NUMBER(15,6)
ec_dol_ytd NUMBER(15,6)
BTM_EC_DATA:
model_id number(5)
org_id number(5)
ec_type varchar2(4) [foreign key references sys_ec_type_tbl.ec_type]
data_dt date
ec_tot_dol_mth NUMBER(15,6)
ec_tot_dol_ytd NUMBER(15,6)
These tables are not normalized. The hierarchy of data is as follows:
EOM_EC_DATA:
FY_YR1 ORG_ID1 EC_TYPE1 DATA_DT1 FY_YR1 ORG_ID1 EC_TYPE1 DATA_DT2 FY_YR1 ORG_ID1 EC_TYPE2 DATA_DT1 FY_YR1 ORG_ID1 EC_TYPE2 DATA_DT2 FY_YR1 ORG_ID2 EC_TYPE1 DATA_DT1
BTM_EC_DATA:
MODEL_ID1 ORG_ID1 EC_TYPE1 DATA_DT1 MODEL_ID1 ORG_ID1 EC_TYPE1 DATA_DT2 MODEL_ID1 ORG_ID1 EC_TYPE2 DATA_DT1 MODEL_ID1 ORG_ID1 EC_TYPE2 DATA_DT2 MODEL_ID1 ORG_ID2 EC_TYPE1 DATA_DT1
I have data as follows:
EOM_EC_DATA:
1999 173 6140 31-OCT-99 10 100 1999 173 6170 31-OCT-99 20 150 1999 174 6140 31-OCT-99 30 120
BTM_EC_DATA:
24 173 6150 31-OCT-99 40 90
24 173 6170 31-OCT-99 25 140
For specific values of fy_yr, model_id, data_dt, and range of values
for ec_type (LIKE '61%'), I want to get all org_id and ec_type values
that are EITHER in EOM_EC_DATA OR in BTM_EC_DATA. The result set
expected from the above data is as follows:
ORG_ID EC_TYPE EC_DOL_MTH EC_DOL_YTD EC_TOT_DOL_MTH EC_TOT_DOL_YTD ------ ------- ---------- ---------- -------------- --------------
173 6140 10 100 NULL NULL 173 6150 NULL NULL 40 90 173 6170 20 150 25 140 174 6140 30 120 NULL NULL
(Plus, I need to join to the sys_ec_type_tbl to get an ec_desc.)
An org_id may exist in one table but not the other. For an org_id that exists in both tables, an ec_type can exist in one but not the other.
I have tried to accomplish this using outer joins, UNIONs, and temp tables in the FROM clause. I have tried so many different things that I can't include them all here.
I would REALLY appreciate any help that anyone can give me with this.
Thanks,
--angela
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Wed Aug 30 2000 - 17:22:50 CDT
 |
 |