Re: help with a SQL/view problem

From: Bill Ferguson <wbfergus_at_gmail.com>
Date: Tue, 16 Feb 2010 11:37:46 -0700
Message-ID: <4025610e1002161037p456bfe74tf0453b509382a6dd_at_mail.gmail.com>

On Tue, Feb 16, 2010 at 11:00 AM, Goulet, Richard <Richard.Goulet_at_parexel.com> 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

Received on Tue Feb 16 2010 - 12:37:46 CST