Stuck on analytics
- From: "Bill Ferguson" <wbfergus@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 18 Feb 2007 15:24:13 -0700
I've been stuck on this for almost two weeks now (and the RMOUG meeting
didn't help).
I have 4 tables (abbreviated) as follows:
CREATE TABLE "DEPOSITS"
( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
"NAME" VARCHAR2(75 BYTE),
"DEV_ST" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"OPER_TP" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"SIG" VARCHAR2(1 BYTE),
CONSTRAINT "DEPOSITS_PK" PRIMARY KEY ("DEP_ID") ENABLE);
CREATE TABLE "COMMODITY"
( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
"LINE" NUMBER(4,0) NOT NULL ENABLE,
"CODE" VARCHAR2(6 BYTE) NOT NULL ENABLE,
"COMMOD" VARCHAR2(25 BYTE) NOT NULL ENABLE,
"QUAL" VARCHAR2(20 BYTE),
"IMPORT" VARCHAR2(5 BYTE) NOT NULL ENABLE,
CONSTRAINT "COMM_PK" PRIMARY KEY ("DEP_ID", "LINE") ENABLE,
CONSTRAINT "COMM_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "DEPOSITS" ("DEP_ID") ON DELETE CASCADE ENABLE);
CREATE TABLE "MODEL_TYPE"
( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
"REC" NUMBER(4,0) NOT NULL ENABLE,
"USGS_NUM" VARCHAR2(15 BYTE) NOT NULL ENABLE,
"MODEL_NAME" VARCHAR2(70 BYTE) NOT NULL ENABLE,
CONSTRAINT "MODEL_TYPE_PK" PRIMARY KEY ("DEP_ID", "REC") ENABLE,
CONSTRAINT "MODEL_TYPE_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "DEPOSITS" ("DEP_ID") ON DELETE CASCADE ENABLE);
CREATE TABLE "LOCATIONS"
( "DEP_ID" NUMBER(12,0) NOT NULL ENABLE,
"LINE" NUMBER(4,0) NOT NULL ENABLE,
"COUNTRY" VARCHAR2(70 BYTE),
"STATE_PROV" VARCHAR2(80 BYTE),
"COUNTY" VARCHAR2(80 BYTE),
CONSTRAINT "LOCATIONS_PK" PRIMARY KEY ("DEP_ID", "LINE") ENABLE,
CONSTRAINT "LOCATIONS_FK" FOREIGN KEY ("DEP_ID")
REFERENCES "USGS"."DEPOSITS_BASE" ("DEP_ID") ON DELETE CASCADE ENABLE);
Actually, the above above are views, but for the purpose of simplicity, I've
merged pertinent information and deleted extraneous 'stuff'.
With the following data:
Insert into DEPOSITS ("DEP_ID","NAME","DEV_ST","OPER_TP","SIG") values
(55555555,'Delete This - Test Entry','Past Producer','Geothermal','N');
Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
values (55555555,5,'ABR','Abrasive',null,'Minor');
Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
values (55555555,4,'W_C','Tungsten','Mill Concentrate','Trace');
Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
values (55555555,3,'CU','Copper',null,'Minor');
Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
values (55555555,6,'AU','Gold',null,'Major');
Insert into COMMODITY ("DEP_ID","LINE","CODE","COMMOD","QUAL","IMPORT")
values (55555555,2,'AG','Silver',null,'Major');
Insert into MODEL_TYPE ("DEP_ID","REC","USGS_NUM","MODEL_NAME") values
(55555555,2,'29a','Quartz pebble conglomerate Au-U (BC name is Paleoplacer
U-Au-PGE-Sn-Ti');
Insert into MODEL_TYPE ("DEP_ID","REC","USGS_NUM","MODEL_NAME") values
(55555555,1,'40a','Detachment-fault-related polymetallic Cu-Au-Ag-Pb-Zn
deposits');
Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
values (55555555,5,'United States','Colorado','Adams');
Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
values (55555555,2,'United States','Montana','Deer Lodge');
Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
values (55555555,3,'United States','Nevada','White Pine');
Insert into LOCATIONS ("DEP_ID","LINE","COUNTRY","STATE_PROV","COUNTY")
values (55555555,4,'United States','Nevada','Lander');
I need to retrieve one line containing the data from DEPOSISTS, and whatever
happens to be the first line (lowest rec or line) in the other tables. As it
is, when I try to retrieve the joined data, I get 40 rows. I can retrieve
just one row from the child tables with the "partition by", but I can't
figure out how to tie them all together.
Anybody know how to join the first row from all the tables together?
Thanks,
Bill Ferguson
Other related posts:
