Re: help with a SQL/view problem

On Tue, Feb 16, 2010 at 11:00 AM, Goulet, Richard
<Richard.Goulet@xxxxxxxxxxx> wrote:
> Bill,
>
>        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-l


Other related posts: