Re: SELECT / UNION Giving Different Results

From: Dereck L. Dietz <dietzdl_at_ameritech.net>
Date: Sun, 01 Jul 2007 01:11:16 GMT
Message-ID: <UaDhi.638$eY.509@newssvr13.news.prodigy.net>

"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 ;

Received on Sat Jun 30 2007 - 20:11:16 CDT