SQL & PL/SQL

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

I have two apparently functionally identical SQL statements and one works and the other does not. Why????

I was trying to create a simple materialized view. I did this:

SQL> create materialized view log on rplwrk with rowid, sequence ( schbat, rplsts )

2  including new values;

Materialized view log created.

SQL>

SQL>

SQL> create materialized view mv_list_wave_entries_1

2  refresh fast on commit

3  as

4  select schbat, count(*) as dummy, count(rplsts) as rem_issht

5  from rplwrk

6  where rplsts != 'D'

7  group by schbat

8  ;

where rplsts != 'D'

*

ERROR at line 6:

ORA-12033: cannot use filter columns from materialized view log on "WMSTST"."RPLWRK"

SQL>

As you can see – it didn’t work. However, after a lot of frustration I tried this:

SQL> create table rplwrk_copy as select * from rplwrk;

Table created.

SQL>

SQL> create materialized view log on rplwrk_copy with rowid, sequence ( schbat, rplsts )

2  including new values;

Materialized view log created.

SQL>

SQL>

SQL> create materialized view mv_list_wave_entries_1

2  refresh fast on commit

3  as

4  select schbat, count(*) as dummy, count(rplsts) as rem_issht

5  from rplwrk_copy

6  where rplsts != 'D'

7  group by schbat

8  ;

Materialized view created.

SQL>

So my materialized view commands are not totally wrong, but somehow it only works on one of the tables. What could be the difference between the table and its copy that causes one materialized view to work and the other not to?