Home » SQL & PL/SQL » SQL & PL/SQL » MATERIALIZED VIEW (TNS for 64-bit Windows: Version 11.2.0.1.0 - Production)
MATERIALIZED VIEW [message #666484] Wed, 08 November 2017 06:42 Go to next message
shawaj
Messages: 50
Registered: January 2016
Member
Hi
I have created materialized view first time and when i query from materialized view than i got an error ORA-01410: invalid ROWID.I do not have any idea why this happening..
Please help me.
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
TABLESPACE SAKET
STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)
NOLOGGING
BUILD IMMEDIATE
REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440
AS 
SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE PM.STYLENO=F.STYLENO(+) 
AND PM.STYLENO=D.STYLENO(+) 
AND D.VCODE=V.VCODE(+) 
AND F.FAB1NO=F1.FAB1NO(+) 
AND F.MAINALLIED(+)='M' 
AND NVL(D.FLAG(+),'S')<>'C' 
AND PM.POSTRING<>'Z'
GROUP BY PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,FAB1NAME,VNAME;
My query was simple
select * from MV_TRPS_MCHNT_FRNT;
Re: MATERIALIZED VIEW [message #666488 is a reply to message #666484] Wed, 08 November 2017 07:53 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
You have a mystery & give us no clues.
We don'y have your tables.
We don't have your data.
So we can't run, test, or debug posted SQL.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: MATERIALIZED VIEW [message #666490 is a reply to message #666488] Wed, 08 November 2017 07:57 Go to previous messageGo to next message
BlackSwan
Messages: 25716
Registered: January 2009
Location: SoCal
Senior Member
>GROUP BY > PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY, DESCRIPTION,FAB1NAME,VNAME;


IMO, every column should be prefixed with table alias to avoid confusion where they originate (but I doubt this will change the problem).
Re: MATERIALIZED VIEW [message #666491 is a reply to message #666490] Wed, 08 November 2017 23:12 Go to previous messageGo to next message
shawaj
Messages: 50
Registered: January 2016
Member
Good morning to all
Thanks for your reply.

I have created materialized view using following syntax
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
 BUILD IMMEDIATE
 REFRESH FORCE 
 ON DEMAND
 ENABLE QUERY REWRITE
AS 
 SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
 PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
 STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM 
 PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE 
 PM.STYLENO=F.STYLENO(+) 
 AND PM.STYLENO=D.STYLENO(+) 
 AND D.VCODE=V.VCODE(+) 
 AND F.FAB1NO=F1.FAB1NO(+) 
 AND F.MAINALLIED(+)='M' 
 AND NVL(D.FLAG(+),'S')<>'C' 
 AND PM.POSTRING<>'Z'
GROUP BY 
 PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,F
 AB1NAME,VNAME;

But i don't know when it will refresh implicitly or explicitly by using
 EXEC DBMS_MVIEW.refresh('MV_TRPS_MCHNT_FRNT');

[Updated on: Wed, 08 November 2017 23:25]

Report message to a moderator

Re: MATERIALIZED VIEW [message #666494 is a reply to message #666491] Thu, 09 November 2017 02:03 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
Quote:
But i don't know when it will refresh implicitly or explicitly by using
You have provided two view definitions: one with a scheduled refresh, one with refresh on demand. Which do you want? Automatic refresh or only when you run the procedure?
Re: MATERIALIZED VIEW [message #666507 is a reply to message #666491] Thu, 09 November 2017 06:28 Go to previous messageGo to next message
shawaj
Messages: 50
Registered: January 2016
Member
shawaj wrote on Wed, 08 November 2017 23:12
Good morning to all
Thanks for your reply.

I have created materialized view using following syntax
CREATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT
 BUILD IMMEDIATE
 REFRESH FORCE 
 ON DEMAND
 ENABLE QUERY REWRITE
AS 
 SELECT PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME M3FULL,POCODE PO,PM.SCODE,
 PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,
 STYLEBUY,DESCRIPTION,FAB1NAME FABRIC,VNAME,SUM(QTY) QTY
FROM 
 PO_MCHNT_BYR PM,FABRIC F,DELIVERY D,VENDOR V,FAB1 F1
WHERE 
 PM.STYLENO=F.STYLENO(+) 
 AND PM.STYLENO=D.STYLENO(+) 
 AND D.VCODE=V.VCODE(+) 
 AND F.FAB1NO=F1.FAB1NO(+) 
 AND F.MAINALLIED(+)='M' 
 AND NVL(D.FLAG(+),'S')<>'C' 
 AND PM.POSTRING<>'Z'
GROUP BY 
 PM.SHCODE,PM.LOCNO,PM.B1CODE,B3FULL,MNAME,POCODE,PM.SCODE,PM.ACTIVE,PM.SHIP_FLAG,PM.POSTRING,PM.MANAGER,PM.UCODE,PM.STYLENO,STYLEBUY,DESCRIPTION,F
 AB1NAME,VNAME;

But i don't know when it will refresh implicitly or explicitly by using
 EXEC DBMS_MVIEW.refresh('MV_TRPS_MCHNT_FRNT');
I am talking about this definition.
I want to refresh automatically
Re: MATERIALIZED VIEW [message #666508 is a reply to message #666507] Thu, 09 November 2017 06:50 Go to previous messageGo to next message
John Watson
Messages: 7148
Registered: January 2010
Location: Global Village
Senior Member
This looks as though you are trolling: being deliberately stupid in order to make people angry. However....

If you create the MV as REFRESH FORCE ON DEMAND, it is not going to refresh automatically. If you create it with REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440 then a refresh job will run every day. Automatically.

--correction: refresh every minute, not every day.

[Updated on: Thu, 09 November 2017 06:51]

Report message to a moderator

Re: MATERIALIZED VIEW [message #666509 is a reply to message #666484] Thu, 09 November 2017 07:10 Go to previous message
EdStevens
Messages: 848
Registered: September 2013
Senior Member
Please, for the sanity of those trying to read you code, learn to format it:

REATE MATERIALIZED VIEW MV_TRPS_MCHNT_FRNT TABLESPACE SAKET STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0) NOLOGGING BUILD IMMEDIATE REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE+1/1440
AS
  SELECT PM.SHCODE,
    PM.LOCNO,
    PM.B1CODE,
    B3FULL,
    MNAME M3FULL,
    POCODE PO,
    PM.SCODE,
    PM.ACTIVE,
    PM.SHIP_FLAG,
    PM.POSTRING,
    PM.MANAGER,
    PM.UCODE,
    PM.STYLENO,
    STYLEBUY,
    DESCRIPTION,
    FAB1NAME FABRIC,
    VNAME,
    SUM(QTY) QTY
  FROM PO_MCHNT_BYR PM,
    FABRIC F,
    DELIVERY D,
    VENDOR V,
    FAB1 F1
  WHERE PM.STYLENO       =F.STYLENO(+)
  AND PM.STYLENO         =D.STYLENO(+)
  AND D.VCODE            =V.VCODE(+)
  AND F.FAB1NO           =F1.FAB1NO(+)
  AND F.MAINALLIED(+)    ='M'
  AND NVL(D.FLAG(+),'S')<>'C'
  AND PM.POSTRING       <>'Z'
  GROUP BY PM.SHCODE,
    PM.LOCNO,
    PM.B1CODE,
    B3FULL,
    MNAME,
    POCODE,
    PM.SCODE,
    PM.ACTIVE,
    PM.SHIP_FLAG,
    PM.POSTRING,
    PM.MANAGER,
    PM.UCODE,
    PM.STYLENO,
    STYLEBUY,
    DESCRIPTION,
    FAB1NAME,
    VNAME;
Previous Topic: update query
Next Topic: oracle external table issue
Goto Forum:
  


Current Time: Tue Nov 21 02:15:18 CST 2017

Total time taken to generate the page: 0.13835 seconds