oedepus
asked on
Create Materialized View
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
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;
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
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
Ok I removed that semi-colon, and I still get the error: Missing keyword. Any other ideas?
this is the new code:
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
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;
this is the new code:
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
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;
Hi,
it seems that the create part of teh statement is not complete...
have a look at this and check out your refresh options.
http://www.ss64.com/ora/mview_c.html
good luck :)
it seems that the create part of teh statement is not complete...
have a look at this and check out your refresh options.
http://www.ss64.com/ora/mview_c.html
good luck :)
ASKER
Sorry, i don't really understand what that page is specifying. I was hoping that someone could help me correct the syntax of this statement. Could someone please help me figure out the correct syntax of creating a materialized view based on the following columns and constraints, with the query?
take out the using index clause and build the index after you get the view in place.
good luck,
daniels
good luck,
daniels
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much for your comments. I did that, and I still get the same error. Missing Keyword. Is there some option, or function, or parameter I am supposed to use when defining the columns of the view. When I take out the column definititions, the view compiles, however, then I am unable to get at the data, because there is not primary key defined.
HELP!
HELP!
Hi,
now that you have closed the question, if you are still having issues, you will need to open another one...
good luck .
now that you have closed the question, if you are still having issues, you will need to open another one...
good luck .
Sorry for not looking closely. Somehow I thought you were trying to create a View, not a Materialized view.
You cannot enforce constraints on materialized views unless they are enforced through constraints on base tables.
You cannot even change the datatypes or its length
All you can do is rename the columns
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID number, TITLE char )
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID number, TITLE char )
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Elapsed: 00:00:00.03
ALEX@PROD > DROP MATERIALIZED VIEW ALEX.SPAH_MV
2 /
DROP MATERIALIZED VIEW ALEX.SPAH_MV
*
ERROR at line 1:
ORA-12003: materialized view "ALEX"."SPAH_MV" does not exist
Elapsed: 00:00:00.01
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID, TITLE )
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
Materialized view created.
You cannot enforce constraints on materialized views unless they are enforced through constraints on base tables.
You cannot even change the datatypes or its length
All you can do is rename the columns
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID number, TITLE char )
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID number, TITLE char )
*
ERROR at line 1:
ORA-00907: missing right parenthesis
Elapsed: 00:00:00.03
ALEX@PROD > DROP MATERIALIZED VIEW ALEX.SPAH_MV
2 /
DROP MATERIALIZED VIEW ALEX.SPAH_MV
*
ERROR at line 1:
ORA-12003: materialized view "ALEX"."SPAH_MV" does not exist
Elapsed: 00:00:00.01
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLEID, TITLE )
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
Materialized view created.
ASKER
Thanks for following up on the question. I appreciate that. OK, so how do I go about enabling a primary key on a materialized view then?
Organization Index uses the primary key in your case, see below:
ALEX@PROD > ALTER TABLE TITLE DISABLE PRIMARY KEY CASCADE;
Table altered.
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLE, TITLE_ID)
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
SELECT TITLE, TITLE_ID FROM TITLE
*
ERROR at line 8:
ORA-12014: table 'TITLE' does not contain a primary key constraint --- PRIMARY KEY NOT FOUND
ALEX@PROD > ALTER TABLE TITLE ENABLE PRIMARY KEY;
Table altered.
Elapsed: 00:00:00.42
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLE, TITLE_ID)
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
Materialized view created.
Elapsed: 00:00:01.78
ALEX@PROD > ALTER TABLE TITLE DISABLE PRIMARY KEY CASCADE;
Table altered.
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLE, TITLE_ID)
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
SELECT TITLE, TITLE_ID FROM TITLE
*
ERROR at line 8:
ORA-12014: table 'TITLE' does not contain a primary key constraint --- PRIMARY KEY NOT FOUND
ALEX@PROD > ALTER TABLE TITLE ENABLE PRIMARY KEY;
Table altered.
Elapsed: 00:00:00.42
ALEX@PROD > CREATE MATERIALIZED VIEW ALEX.SPAH_MV(TITLE, TITLE_ID)
2 ORGANIZATION INDEX
3 TABLESPACE "USERS"
4 BUILD IMMEDIATE
5 USING INDEX
6 REFRESH FORCE ON DEMAND
7 AS
8 SELECT TITLE, TITLE_ID FROM TITLE
9 /
Materialized view created.
Elapsed: 00:00:01.78
you have a semi colon in the command :
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
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); <--------------- SEMI COLON HERE
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;
:)