create view error !! (missing parenthesis error)

Hi,

I am having tables(more than 10) which are related using foreign key and primary key relationship.

Example:
Table1:
T1Prim T1Col1 T1Col2

Table2
T2For T2Prim T2Col1 T2Col2 T2Col3
(here T2For will have value same as T1Prim and in my design it has same column name i.e. T1Prim)

Table3
T3For T3Prim T3Col1 T3Col2 T3Col3
(here T3For will have value same as T2Prim)

and so on.

I want to write a view where by i can see everything in one view.
When I wrote view taking two tables it gave me no errors but when i tried to put more tables and its columns into it i got error like (missing parenthesis etc).

I wrote view like

/*-------------------------------------------------------------------------------

Create or replace view test (T1Prim,T1Col1, T1Col2,T2Prim,T2Col1 T2Col2, T2Col3, T3Prim,T3Col1, T3Col2, T3Col3)
As
Select Table1.T1Prim,Table1.T1Col1, Table1.T1Col2, Table2.T2Prim, Table2.T2Col1, Table2.T2Col2, Table2.T2Col3, Table3.T3Prim,Table3.T3Col1, Table3.T3Col2, Table3.T3Col3

From Table1, Table2, Table3
where Table1.Prim = Table2.For
and Table2.Prim = Table3.For

--------------------------------------------------------------------------------*/

I am using sql navigator which puts the parenthesis both right and left and i am getting error like "missing right parenthesis". But when i have only two able in the above statement it works fine. And i have given fully qualified name for the column names between the parenthesis to avoid any conflict.

Can anyone please tell me where i m goofing.

Further if there are many rows in Table2 and Table3 onwards and if want to get the first row of data only how do i write the my veiw statement.

Any help would be appreciated.
Eagerly waiting for reply.

Regards,

I got the error. Since column names were same in the few table thats why oracle was not able to add it properly.

But when i did select * from MyView i didn't got anything. All the columns are blank wherewas when i do select * Table1 i m getting valid data.

Is my login wrong ?
How do i get only the first row of data using view like this ?

Eagerly waiting for reply.

I assume that you want to create a view on basis of three(3) tables like table1,table2,table3 but you want to use only first record of each table2, and table3 to join with table3(no matter how many records table3 has). You can write query like that:
CREATE OR REPLACE VIEW my_view (col1,col2,col3) AS
SELECT col1,col2,col3
FROM table1, (SELECT col1,col2,col3 FROM table2
WHERE rownum<2) b,
(SELECT col1,col2,col3 FROM table2
WHERE rownum<2) c,
WHERE table1.col1 = b.col1
AND b.col1 = c.col1

This is not an actual query that you wanted, but it will give you an idea to make your own query.


Message: Make your life easy, make others life easier.


Regards

Hi,

Thanks for replying.
I finally got it running i was having one logical error in typing column name at one place.

Thanks for providing alternate way to do this.

Regards,
Dipesh