#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0

    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,
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    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.
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    12
    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
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    29
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo