Hello Tom,

Is it possible to create a snapshot(materialized view) using decode function in the select query. If so can you please give me the syntax.

What we are trying to do is, we have these tree tables

CREATE TABLE HEAP_TEST
(TABLNAME VARCHAR2(50),
W6KEY_0LD NUMBER(10),
W6KEY_NEW NUMBER(10));


CREATE TABLE REF_TEST1
(W6KEY NUMBER(10),
SNAME VARCHAR2(25));

ALTER TABLE REF_TEST1 ADD PRIMARYKEY PK_RT1(W6KEY)

CREATE TABLE REF_TEST2
(W6KEY NUMBER(10),
SNAME VARCHAR2(25));

ALTER TABLE REF_TEST2 ADD PRIMARYKEY PK_RT2(W6KEY)



CREATE SNAPSHOT LOG ON REF_TEST1 ;

INSERT INTO HEAP_TEST values('REF_TEST1',100,200);
INSERT INTO HEAP_TEST values('REF_TEST1',101,201);
INSERT INTO HEAP_TEST values('REF_TEST1',102,202);

Create snapshot REF_TEST2 on prebuilt table refresh FAST with primary key start with sysdate next sysdate + 1/288 for update as SELECT DECODE( A.W6KEY,100,200,101,201,102,202,A.W6KEY),A.SNAME
FROM REF_TEST1 A ,HEAP_TEST B
WHERE A.W6KEY = B.W6KEY_OLD (+);

We only want new values of w6key in REF_TEST1 table.


Please help.

Thanks
Humaira.

Tom,

A quick question. Where in the Data Dictionary is the name of the RUPD$ table held ? The MLOG$ table name is held in the DBA_MVIEW_LOGS view, but I can't find any reference to the RUPD$ table. Is is somewhere accessible ?

The reason I'm asking is, I want to dynaimcally run through all objects in a schema (USER_OBJECTS) and perform a task, but want to exlcude the mview log tables (they're filed as object type TABLE). This is fine as I can find the MLOG$ tablenames in USER_MVIEW_LOGS and exlcude them but I've had to hard code the RUPD$ as follows :-

SELECT object_name, object_type
FROM user_objects
WHERE object_type NOT IN ('INDEX', 'DATABASE LINK')
AND object_name NOT LIKE 'RUPD$%'
AND object_name NOT IN (SELECT log_table
FROM user_mview_logs);

Thanks in advance!

Tom,

Thanks for the quick reply to my quick question :o)

OK, it then begs the question as to how Oracle itself knows what the RUPD$ tablename is when you issue a

SQL> DROP MATERIALIZED VIEW LOG ON <tablename> ;

command ?

Especially if the <tablename> is 30chars long, the RUPD$ tablename is truncated and sometimes has a "1", "2" etc on the end if the name is already taken.

Sorry if I'm digging too deep here, and I should just stick to the hard-coding fudge.  Feel free to just dismiss me in your usual scathing manner :D

Kind regards,
David.