troubleshooting Question

Create Materialized View

Avatar of oedepus
oedepus asked on
Oracle Database
11 Comments1 Solution1002 ViewsLast Modified:
I keep getting an error message when I try to create this materialized view that says I am missing a key word.  Can someone help me with the syntax?

  CREATE MATERIALIZED VIEW "LP06DEV"."SPAH_MV"
  TABLESPACE "LODE2006DAT"
  BUILD IMMEDIATE
  USING INDEX
  REFRESH FORCE ON DEMAND
(UIDSERVICEPOINT NUMBER(19) not null,
  STARTTIME DATE not null,
  STOPTIME DATE,
  METERTYPE CHAR(1) not null,
  UIDRES number(19) not null,
  UIDCPNODE number(19) not null,
  UIDASSET number(19) not null,
  UIDSUPPLIERPRODUCT number(19) not null,
  UIDELECTRICASSET number(19),
  UIDPROFILE number(19) not null,
  UIDLOSS number(19) not null,
  UIDSUPPLYCLASS number(19) not null,
  ENERGIZED char(1) not null,
  UIDASHSEGMENT number(19) not null,
  LSUSER varchar(32),
  LSTIME date default sysdate,
  constraint PK_SPAH_MVIEW primary key (uidservicepoint, starttime, metertype),
  constraint CK_SPAH_STARTSTOP check (STARTTIME < STOPTIME),
  constraint CK_SPAH_VALIDSTART check (to_char(STARTTIME, 'hh24:mi:ss') = '00:00:00'),
  constraint CK_SPAH_VALIDSTOP check (to_char(STOPTIME, 'hh24:mi:ss') = '23:59:59'),
  constraint FK_ASH_UIDSERVICEPOINT foreign key (UIDSERVICEPOINT) references LSSERVICEPOINT,
  constraint CK_ASH_METERTYPE check (METERTYPE in ('I','S')),
  constraint CK_ASH_ENERGIZED check (ENERGIZED in ('E','D')),
  constraint FK_ASH_RES foreign key (UIDRES) references RES,
  constraint FK_ASH_CPNODE foreign key (UIDCPNODE) references CPNODE,
  constraint FK_ASH_ASSET foreign key (UIDASSET) references ASSET,
  constraint FK_ASH_SUPPLIERPRODUCTCODE foreign key (UIDSUPPLIERPRODUCT) references SUPPLIERPRODUCT,
  constraint FK_ASH_ELECTRICASSETCODE foreign key (UIDELECTRICASSET) references ELECTRICASSET,
  constraint FK_ASH_PROFILECODE foreign key (UIDPROFILE) references PROFILECLASS,
  constraint FK_ASH_LOSSCODE foreign key (UIDLOSS) references LOSSCLASS,
  constraint FK_ASH_SUPPLYCLASSCODE foreign key (UIDSUPPLYCLASS) references SUPPLYCLASS,
  constraint FK_ASH_ASHSEGMENT foreign key (UIDASHSEGMENT) references ASHSEGMENT)
  organization index parallel);
  AS
select sph.uidservicepoint as UIDSERVICEPOINT,
sph.starttime as STARTTIME,
sph.stoptime as STOPTIME,
sph.metertype as METERTYPE,
sph.uidres as UIDRES,
sph.uidcpnode as UIDCPNODE,
sph.uidasset as UIDASSET,
sph.uidsupplierproduct as UIDSUPPLIERPRODUCT,
sph.uidelectricasset as UIDELECTRICASSET,
sph.uidprofile as UIDPROFILE,
sph.uidloss as UIDLOSS,
sph.uidsupplyclass as UIDSUPPLYCLASS,
'E' as ENERGIZED,
ash.uidashsegment as UIDASHSEGMENT,
user as LSUSER,
sysdate as LSTIME
from SPAH_VW sph, ashsegment ash
where sph.uidres = ash.uidres
and sph.uidcpnode = ash.uidcpnode
and sph.uidasset = ash.uidasset
and sph.uidsupplierproduct = ash.uidsupplierproduct
and sph.uidelectricasset = ash.uidelectricasset
and sph.uidprofile = ash.uidprofile
and sph.uidloss = ash.uidloss
and sph.uidsupplyclass = ash.uidsupplyclass
group by sph.uidservicepoint,
sph.starttime,
sph.stoptime,
sph.metertype,
sph.uidres,
sph.uidcpnode,
sph.uidasset,
sph.uidsupplierproduct,
sph.uidelectricasset,
sph.uidprofile,
sph.uidloss,
sph.uidsupplyclass,
ash.uidashsegment;
ASKER CERTIFIED SOLUTION
paquicuba

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros