OraFAQ Forum: SQL & PL/SQL » MATERIALIZED VIEW

Home » SQL & PL/SQL » SQL & PL/SQL » MATERIALIZED VIEW (TNS for 64-bit Windows: Version 11.2.0.1.0 - Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

MATERIALIZED VIEW [message #666484]

Wed, 08 November 2017 06:42

shawaj
Messages: 89
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;

Report message to a moderator

Re: MATERIALIZED VIEW [message #666488 is a reply to message #666484]

Wed, 08 November 2017 07:53

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: MATERIALIZED VIEW [message #666490 is a reply to message #666488]

Wed, 08 November 2017 07:57

BlackSwan
Messages: 26766
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).

Report message to a moderator

Re: MATERIALIZED VIEW [message #666491 is a reply to message #666490]

Wed, 08 November 2017 23:12

shawaj
Messages: 89
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

John Watson
Messages: 8929
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?

Report message to a moderator

Re: MATERIALIZED VIEW [message #666507 is a reply to message #666491]

Thu, 09 November 2017 06:28

shawaj
Messages: 89
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

Report message to a moderator

Re: MATERIALIZED VIEW [message #666508 is a reply to message #666507]

Thu, 09 November 2017 06:50

John Watson
Messages: 8929
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

EdStevens
Messages: 1376
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;

Report message to a moderator

Previous Topic:	query datewise howmany records
Next Topic:	update query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 20:18:06 CDT 2024