lost data when creating materialized view

From: hastenthunder <hastenthunder_at_hotmail.com>
Date: Wed, 27 Oct 2004 16:38:46 -0400
Message-ID: <qHTfd.1747$Ny6.2928@mencken.net.nih.gov>

Hi experts,

I'm trying to create a materialized view in Oracle 10.1.0.2.0 on Red-Hat Enterprise 3 using the following syntax

    CREATE Materialized View MV_TEST

        tablespace seer_mv
        build immediate
        refresh on demand

    as (sql-statement);

SQL> @ sql-statement;
18410 rows are returned.

SQL> select count(*) from MV_TEST
9661 rows are returned

What could possible cause that? Looks like a bug to me, cause the materialized view should be created with the data produced by the sql-statement.

I'm posting the sql-statement at end cause its kind of long.

Thanks

sql-statement:

SELECT*
FROM ctc_med_pract_care mpc JOIN

      person p ON (mpc.person_id = p.person_id) LEFT OUTER JOIN
     lkup_title on (p.title = lkup_title.title)
WHERE (mpc.ctc_med_pract_care_id, mpc.ctc_id) IN
                                      (SELECT MIN( ctc_med_pract_care_id) ,
                                                       ctc_id
                                         FROM ctc_med_pract_care
                                         WHERE (ctc_id,follow_up_physician)

IN

(SELECT ctc_id,

MAX( follow_up_physician)

FROM ctc_med_pract_care cmpc JOIN

person pe ON (cmpc.person_id = pe.person_id) JOIN

mv_pat_join_ctc pjc ON (pjc.ctc_id = cmpc.ctc_id)

WHERE (pe.active = 1 AND pref_contact_method in (SELECT contact_method FROM lkup_contact_method WHERE value in ( 'Letter', 'E-mail', 'Fax')))

AND pjc.vital_status != 4

AND pjc.ctc_status = 1

AND seerdms_report.compare_real_date_early_null
(date_of_last_contact_yyyy,date_of_last_contact_mm,date_of_last_contact_dd,a
dd_months (sysdate,- 14)) = 1

AND pat_id NOT IN (SELECT pat_id FROM mv_patient_with_fac_fup)

GROUP BY ctc_id                                                                             

   )

                                         GROUP BY ctc_id
                                         )

); Received on Wed Oct 27 2004 - 15:38:46 CDT