Re: help with a SQL/view problem
Date: Tue, 16 Feb 2010 11:37:46 -0700
On Tue, Feb 16, 2010 at 11:00 AM, Goulet, Richard <Richard.Goulet_at_parexel.com> wrote:
> Well your first mistake is running on windoze. As for the
> second, first I need to know what the definition of deposits_base is.
Okay, the rough structure of DEPOSITS_BASE is (sorry for bad HTML formatting):
CREATE TABLE DEPOSITS_BASE
DEP_ID NUMBER(12) NOT NULL, REC_TP VARCHAR2(10 BYTE) DEFAULT 'Site', DEV_ST VARCHAR2(25 BYTE) NOT NULL, DEP_TP VARCHAR2(120 CHAR), PLANT_TP VARCHAR2(30 BYTE), PLANT_IDENT VARCHAR2(40 BYTE), OPER_TP VARCHAR2(30 BYTE) NOT NULL, MIN_METH VARCHAR2(60 BYTE), MILL_METH VARCHAR2(60 BYTE), YFP_BA VARCHAR2(1 BYTE), YR_FST_PRD NUMBER(4), YLP_BA VARCHAR2(1 BYTE), YR_LST_PRD NUMBER(4), DISC_METH VARCHAR2(40 BYTE), DY_BA VARCHAR2(1 BYTE), DISC_YR NUMBER(4), PROD_YRS VARCHAR2(300 BYTE), DISCR VARCHAR2(400 BYTE), INSERTED_BY VARCHAR2(30 BYTE) DEFAULT user NOT NULL, INSERT_DATE DATE DEFAULT sysdate NOT NULL, UPDATED_BY VARCHAR2(30 BYTE), UPDATE_DATE DATE,
and a few other fields.
DEP_ID is the primary key, and is the only constant across each of the child tables (with the exception of the other 'housekeeping' fields). The child tables will have a few extra fields like REC or YEAR to help uniquely identify each row, but everything has DEP_ID to tie back to the parent table.
So my problem (as it appears to me), is how to get the count(distinct DEP_ID) as it goes through each table/field and performs the final calculation for that row.
Again, sorry for the bad HTML formatting, but this is what I get returned for the first few rows (fields) in DEPOSITS_BASE. The last column is the one with the questionable number:
TABLE_NAME COLUMN_NAME DATA_TYPE IS_REQUIRED DEFAULT_VALUE NUM_DISTINCT NUM_NULLS LAST_ANALYZED NUM_ROWS Table Pct Populated DB Pct Populated
DEPOSITS_BASE DEP_ID NUMBER * 305784 0 11-FEB-10 305784 100 100 DEPOSITS_BASE REC_TP VARCHAR2 'Site' 4 0 11-FEB-10 305784 100 100 DEPOSITS_BASE DEV_ST VARCHAR2 * 6 0 11-FEB-10 305784 100 100 DEPOSITS_BASE DEP_TP VARCHAR2 1225 268393 11-FEB-10 305784 12 12 DEPOSITS_BASE PLANT_TP VARCHAR2 12 302701 11-FEB-10 305784 1 1 DEPOSITS_BASE PLANT_IDENT VARCHAR2 29 304139 11-FEB-10 305784 1 1
From a child table:
OREBODIES_BASE DEP_ID NUMBER * 58621 0 11-FEB-10 62368 100 20 OREBODIES_BASE LINE NUMBER * 18 0 11-FEB-10 62368 100 20 OREBODIES_BASE OREBODY_NAME VARCHAR2 873 61125 11-FEB-10 62368 2 0 OREBODIES_BASE FORM VARCHAR2 4205 30620 11-FEB-10 62368 51 10 OREBODIES_BASE AREA NUMBER 803 57221 11-FEB-10 62368 8 2 OREBODIES_BASE AREA_U VARCHAR2 1 57225 11-FEB-10 62368 8 2
So, the child table has 100% popultaion of the DEP_ID and LINE fields, but shows as 20% population of the database (based upon the 305784 unique records in the parent table), while this child table actually has 58621 distinct dep_ids, which is what I need the last column to get computed uopn. Since the database is so small, in the grand scheme of things it may actually work out to around a 1%-2% difference, but the scientists are sticklers on these small details.
-- -- Bill Ferguson -- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 16 2010 - 12:37:46 CST