Error on Materialized view : subquery or named view in FROM list even after view merging

From: Victor K. <nicolas.karasek_at_gmail.com>
Date: 25 Jul 2006 05:47:54 -0700
Message-ID: <1153831674.553834.160430@s13g2000cwa.googlegroups.com>

Hello,
i'm trying to create a mview with FAST refresh, ON COMMIT and a query with UNION ALL.
I succeed in creating some mview of this type, so i assume mview logs creation is ok.
So i tried another another query, it didn't work and DBMS_VIEW_EXPLAIN_MVIEW give me the following tips :

REFRESH_FAST_AFTER_INSERT F subquery or named view in FROM list even after view merging

The concerned query is :

SELECT
  d.ID_PROP as ID_LINE,
'RT' as TYPE,

  a.TYPE_SS_CAT as CAT,
  COUNT(*) as TOTAL,
  COUNT(a.TYPE_SS_CAT) as VALEUR,
'attn' as TYPE_COUNT,

  d.MARCHE_ID,
  1 as umarker
FROM
  ATTN a inner join DBMV d on a.REF_INT_DBMV = d.ID WHERE

  d.ID_PROP IS NOT NULL AND
  a.INDICATOR = 1 AND
  a.TYPE_PROP = 'RT' AND
  d.MARCHE_ID = 1

group by a.TYPE_SS_CAT, d.MARCHE_ID, d.ID_PROP union all
SELECT
  d.ID_FG_LINE as ID_LINE,
'FG' as TYPE,

  a.TYPE_SS_CAT as CAT,
  COUNT(*) as TOTAL,
  COUNT(a.TYPE_SS_CAT) as VALEUR,
'attn' as TYPE_COUNT,

  d.MARCHE_ID,
  2 as umarker
FROM
  ATTN a inner join DBMV d on a.REF_INT_DBMV = d.ID WHERE

  d.ID_FG_LINE IS NOT NULL AND
  a.INDICATOR= 1 AND
  a.TYPE_PROP = 'FG' AND
  d.MARCHE_ID = 1

group by a.TYPE_SS_CAT, d.MARCHE_ID, d.ID_FG_LINE

As english isn't my mothertongue, i don't fully understand the error message and what it is the problem.
Oracle version is 9.2.

does anyone see the problem ?

regards,

-- 
Victor

Received on Tue Jul 25 2006 - 07:47:54 CDT