View Creation Syntax Issue?

[gbarnes]

Hi all,

I am having an issue when trying to create a view based on 3 tables. The database is Oracle10g

the SQL looks like the following:

[SQL]

CREATE VIEW MARKETREPORTVIEW AS SELECT * FROM
(
SELECT * FROM
(
SELECT ROS.RID AS TREPORTID ,
ROS.BID AS TBID ,
ROS.SGROUPID AS TGROUPID ,
ROS.SGROUPNAME AS TSGROUPNAME ,
ROS.SDR AS TSDR ,
ROP.UDA1TITLE AS TUDA1TITLE ,
ROP.UDA1VAL AS TUDA1VAL ,
FROM ROP INNER JOIN ROS ON ROP.POOLID =
ROS.SGROUPID
AND
ROP.RTID = ROS.RTID
) AS TESTTABLE
)
TESTTABLE INNER JOIN AFRPT ON ARFRPT.CID = TESTTABLE.TBID AND ARPT.REPORTID = TESTTABLE.TREPORTID ;

[/SQL]

The error that I get is 'ORA-00907: missing right parenthesis' at the ') AS TESTTABLE'.

I also tried the following for the FROM clauses...

[SQL]

//...

FROM ROP, ROS WHERE ROP.POOLID = ROS.SGROUPID (+) AND ROP.REPORTID = ROS.REPORTID (+)
) AS TESTTABLE
)TESTTABLE, AFRPT WHERE AFRPT.CID = TESTTABLE.TBID (+) AND AFRPT.REPORTID = TESTTABLE.TREPORTID (+) ;


[/SQL]

From what I can tell, the SQL should be correct. Could anyone provide any clues as to why the syntax validator is not liking it?

Thanks

[SQLNAVIGATOR]

Hi all,

I am having an issue when trying to create a view based on 3 tables. The database is Oracle10g

the SQL looks like the following:

[SQL]

CREATE VIEW MARKETREPORTVIEW AS SELECT * FROM
(
SELECT * FROM
(
SELECT ROS.RID AS TREPORTID ,
ROS.BID AS TBID ,
ROS.SGROUPID AS TGROUPID ,
ROS.SGROUPNAME AS TSGROUPNAME ,
ROS.SDR AS TSDR ,
ROP.UDA1TITLE AS TUDA1TITLE ,
ROP.UDA1VAL AS TUDA1VAL ,
FROM ROP INNER JOIN ROS ON ROP.POOLID =
ROS.SGROUPID
AND
ROP.RTID = ROS.RTID
) AS TESTTABLE
)
TESTTABLE INNER JOIN AFRPT ON ARFRPT.CID = TESTTABLE.TBID AND ARPT.REPORTID = TESTTABLE.TREPORTID ;

[/SQL]

The error that I get is 'ORA-00907: missing right parenthesis' at the ') AS TESTTABLE'.

I also tried the following for the FROM clauses...

[SQL]

//...

FROM ROP, ROS WHERE ROP.POOLID = ROS.SGROUPID (+) AND ROP.REPORTID = ROS.REPORTID (+)
) AS TESTTABLE
)TESTTABLE, AFRPT WHERE AFRPT.CID = TESTTABLE.TBID (+) AND AFRPT.REPORTID = TESTTABLE.TREPORTID (+) ;


[/SQL]

From what I can tell, the SQL should be correct. Could anyone provide any clues as to why the syntax validator is not liking it?

Thanks

HI , Here i am giving an example .You need to correct your syntax.When you are creating a view on some tables , you need to be define the attributes on view level.


CREATE OR REPLACE VIEW soda_beverage_dependent (
manufacturer_id,
name,
notes )
AS
select s.manufacturer_id, s.name, b.notes from
soda_manufacturer s, beverage_class b
where s.notes = b.notes
/


cheers
SN

[gbarnes]

HI , Here i am giving an example .You need to correct your syntax.When you are creating a view on some tables , you need to be define the attributes on view level.


CREATE OR REPLACE VIEW soda_beverage_dependent (
manufacturer_id,
name,
notes )
AS
select s.manufacturer_id, s.name, b.notes from
soda_manufacturer s, beverage_class b
where s.notes = b.notes
/


cheers
SN

Thanks SN, I see where I was wrong.