Dereck L. Dietz wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1183213601.534028_at_bubbleator.drizzle.com...
>> Dereck L. Dietz wrote:
>>> We've had a very strange (and possibly troubling) event happen.
>> I've read Mark and Vladimir's responses to you and agree with them
>> but I'd like to pile on.
>>
>> In addition to version ... Where's your SQL statement? We are in
>> no position to evaluate what you are doing without seeing DDL and DML.
>>
>> Before you repost try this:
>>
>> Insert into each table one record that you know will connect to the
>> other 19 using your query but not to any other existing record. Test
>> your query then.
>>
>> If you are still having problems post a test case with version, explain
>> plan created using dbms_xplan, and a complete copy of the actual SQL.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan_at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org
>
> Version - 10.2.0.3.0
> OS - Windows 2003 Server
> Memory - 8 GB
>
> In the production version we identified a row from the HMP_COPD_CLAIMS table
> which didn't make it into the T_HB_DATES table (and hence not into the
> production DATES table).
>
> In the test where I re-ran the code below the test table I created contained
> the row which didn't make
> it into the T_HB_DATES table before.
>
> Once we identified that there were missing rows I took my test table and
> compared it against the production dates table and created a table of the
> missing rows. When I did a count of rows grouped by Type it showed that
> missing rows spanned all the tables from the select statement and weren't
> just specific to the HMP_COPD_CLAIMS table.
>
> There has been no changes made to the source tables between the production
> run and my test run.
>
> I don't have off-site access to the database so I won't be able to run
> anything else until Monday.
>
> Also, this is inherited code so please don't blame me for the use of
> reserved words as column names.
>
>
> CREATE TABLE T_HB_DATES
> NOLOGGING
> AS
> ---------------------------------------
> --ASTHMA--
> ---------------------------------------
> --GATHER ALL ASHTMA AMBULATORY VISITS--
> ---------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> 'AM' AS TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_AS_BCN_POS_AM_TB A
> UNION
> ------------------------
> --GATHER ALL ASTHMA RX--
> ------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> TYPE,
> A."DATE" AS "DATE",
> DTSOURCE
> FROM T_HB_AS_RX_TB A
> UNION
> -------------------------------
> --GATHER ALL ASHTMA ED AND IP--
> -------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> CASE
> WHEN TRIM(SOURCE)='E' THEN 'EA'
> WHEN TRIM(SOURCE)='I' THEN 'IA'
> END AS TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_AS_BCN_ER_IP_TB A
> UNION
> ------------------------------------
> --CHF--
> ------------------------------------
> --GATHER ALL CHF AMBULATORY VISITS--
> ------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> 'OC' AS TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_CH_BCN_POS_AM_TB A
> UNION
> ---------------------
> --GATHER ALL CHF RX--
> ---------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> TYPE,
> A."DATE" AS "DATE",
> DTSOURCE
> FROM T_HB_CH_ACE_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> 'DU' AS TYPE,
> A."DATE" AS "DATE",
> DTSOURCE
> FROM T_HB_CH_DIU_TB A
> UNION
> ----------------------------
> --GATHER ALL CHF ED AND IP--
> ----------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> CASE
> WHEN TRIM(SOURCE)='E' THEN 'EC'
> WHEN TRIM(SOURCE)='I' THEN 'IC'
> END AS TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_CH_BCN_POS_ER_IP_TB A
> UNION
> ----------------------------------------
> --DIABETES--
> ----------------------------------------
> --GATHER ALL DI AMBULATORY--
> ----------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> 'OD' AS TYPE,
> A."DATE" AS "DATE",
> '1' AS DTSOURCE
> FROM T_HB_DI_BCN_POS_AM_TB A
> UNION
> ---------------------------
> --GATHER ALL DI ED AND IP--
> ---------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> CASE
> WHEN TRIM(SOURCE)='E' THEN 'ED'
> WHEN TRIM(SOURCE)='I' THEN 'ID'
> END AS TYPE,
> A."DATE" AS "DATE",
> '1' AS DTSOURCE
> FROM T_HB_DI_BCN_POS_ER_IP_TB A
> UNION
> ---------------------------
> --GATHER DIABETES EYE EXAMS
> ---------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE",
> A.DTSOURCE
> FROM T_HB_DI_EYEDATES1_TB A
> UNION
> -----------------------------------
> --GATHER DIABETES NEPHROLOGY VISITS
> -----------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE",
> DTSOURCE
> FROM T_HB_DI_NEPHDATES_TB A
> UNION
> ---------------------
> --GATHER BREAST EXAMS
> ---------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE",
> A.DTSOURCE
> FROM T_HB_BR_SCRN_BCN_POS_TB A
> UNION
> ---------------------
> --GATHER CT AND MRI
> ---------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE",
> A.DTSOURCE
> FROM T_HB_CT_MRI_BCN_TB A
> UNION
> -----------------------
> --GATHER CERVICAL EXAMS
> -----------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE",
> A.DTSOURCE
> FROM T_HB_CE_SCRN_BCN_POS_TB A
> UNION
> ----------------------------------------
> --Cardiovascular DISEASE--
> ----------------------------------------
> --GATHER ALL CVD AMBULATORY--
> ----------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_CVC_AM_TB A
> UNION
> ----------------------------
> --GATHER ALL CVD ED AND IP--
> ----------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_CVA_DXPRO_IP_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_CVA_PROC_IP_TB A
> UNION
> ---------------------------------------------
> --MIGRAINE--
> ---------------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_MG_BCN_POS_AM_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_MG_BCN_ER_IP_TB A
> UNION
> ---------------------------------------------
> --TENSION HEADACHE(MIGRAINE)--
> ---------------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_TH_BCN_POS_AM_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_TH_BCN_ER_IP_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_TH_H3F_TB A
> UNION
> ---------------------------------------------
> --LOW BACK PAIN--
> ---------------------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_LB_BCN_POS_AM_TB A
> UNION
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.SOURCE AS DTSOURCE
> FROM T_HB_LB_BCN_ER_TB A
> UNION
> ---------------------------
> -- WELL CHILD VISITS --
> ---------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_WV_BCN_TB A
> UNION
> ----------------
> --ADULT ACCESS--
> ----------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_AA_BCN_POS_TB A
> UNION
> ---------------------------
> -- COLORECTAL INCLUSIONS --
> ---------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_CR_BCN_POS_INCL_TB A
> UNION
> ---------------------------
> -- BETA BLOCKER RX --
> ---------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_BB_RX_TB A
> UNION
> ------------------------------
> -- CHILD AND ADOL IMMUNS--
> ------------------------------
> SELECT DISTINCT
> A.PERSON_ID,
> A.MBR_SYS_KEY,
> A.MBR_KEY,
> A.TYPE,
> A."DATE" AS "DATE",
> A.DTSOURCE
> FROM T_HB_IMMUNS_TB A
> -------------------------------
> -- COPD
> -------------------------------
> UNION
> SELECT DISTINCT person_id,
> mbr_sys_key,
> mbr_key,
> service_type_code,
> srv_dt_from AS "DATE",
> dtsource
> FROM hmp_copd_claims
> UNION
> SELECT DISTINCT person_id,
> mbr_sys_key,
> mbr_key,
> service_type_code,
> srv_dt_from AS "DATE",
> dtsource
> FROM hmp_copd_er_ip
> ----------------------------------------------------------------------------
> -
> -- Bariatric Surgery
> ----------------------------------------------------------------------------
> -
> UNION
> SELECT DISTINCT person_id,
> mbr_sys_key,
> mbr_key,
> service_type_code,
> srv_dt_from AS "DATE",
> dtsource
> FROM hmp_bariatric
> ;
Ugly and a horrible misuse of keywords as you obviously know but there
is not a WHERE clause in sight which means this query is not performing
any filter of anything other than via your use of DISTINCT and UNION.
So a few quick questions ... why DISTINCT and why UNION rather than
UNION ALL? Change UNION to UNION ALL and I'll bet you get what you think
are your missing records.
BTW: The schema design is an object lesson in what not to do. Which
company's product is this?
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Jun 30 2007 - 23:15:51 CDT
