11 Replies Latest reply: Sep 8, 2005 11:35 AM by 245482 RSS

    Problems with Materialized view  - ORA-00907: missing right parenthesis

    264787
      Hi,
      according to the oracle docs, this syntax should work. However I get a ORA-00907: missing right parenthesis error for this.
      btw. the sql select statement works on its own.
      I am stumped - anyone offer any suggestions?
      Thanks
      Conrad

      CREATE MATERIALIZED VIEW EXHIBITS_VIEW
                     REFRESH ON COMMIT
                ENABLE QUERY REWRITE
      AS
                SELECT FTS_EXHIBIT.ID as EXHIBIT_ID, FTS_EXHIBIT.EXHIBIT_REF, FTS_EXHIBIT.CASE_REF, EXHIBIT_ITEM.ID AS EXHIBIT_ITEM_ID, EXHIBIT_ITEM.NAME,
                EXHIBIT_ITEM.TELEPHONE_NUMBER, EXHIBIT_ITEM.TXT_MESSAGE, FTS_CONTACT.CONTACT_NAME
      FROM EXHIBIT_ITEM, FTS_EXHIBIT left join FTS_CONTACT on FTS_EXHIBIT.CONTACT_ID = FTS_CONTACT.ID
      WHERE FTS_EXHIBIT.ID = EXHIBIT_ITEM.EXHIBIT_ID;
        • 1. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
          2889
          a quick search for ORA-907 on metalink gives quite impressive results.

          mostly it's about false ORA-907 messages when using ANSI-joins.
          • 2. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
            264787
            by quite impressive - I assume you mean a lot of bug reports that may or may not mean I have come across a bug.

            all I was after was some confirmation as to the validity of the syntax but I suppose (having used OAS for a while) I should have assumed it was a bug somewhere first of all.

            Thanks
            Conrad
            • 3. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
              245482
              You're using a combination of one "old fashioned" join and one ANSI join. Do you get the same problem if you define the mv as
              SELECT fts_exhibit.id AS exhibit_id,
                     fts_exhibit.exhibit_ref,
                     fts_exhibit.case_ref,
                     exhibit_item.ID AS exhibit_item_id,
                     exhibit_item.name,
                     exhibit_item.telephone_number,
                     exhibit_item.txt_message,
                     fts_contact.contact_name
                FROM exhibit_item, 
                     fts_exhibit, 
                     fts_contact
               WHERE fts_exhibit.id = exhibit_item.exhibit_id
                 AND fts_exhibit.contact_id = fts_contact.id;
              Message was edited by:
              scott.swank
              • 4. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                264787
                Scott,
                Thanks for this, but I am not sure I know what you mean.
                The code you have posted doesn't do the left join so I don't get all the records I need i.e. fts_exhibit rows that don't have a contact_id, this sql only gets rows that are in the fts_exhibit table that have a contact and also have exhibit_items. what I need is all fts_exhibt rows, contacts (if any) and all exhibit_items.
                Conrad
                • 5. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                  Jan-Marcel
                  Then change Scott's query to:
                  SELECT fts_exhibit.id AS exhibit_id,
                         fts_exhibit.exhibit_ref,
                         fts_exhibit.case_ref,
                         exhibit_item.ID AS exhibit_item_id,
                         exhibit_item.name,
                         exhibit_item.telephone_number,
                         exhibit_item.txt_message,
                         fts_contact.contact_name
                    FROM exhibit_item, 
                         fts_exhibit, 
                         fts_contact
                   WHERE fts_exhibit.id = exhibit_item.exhibit_id
                     AND fts_exhibit.contact_id = fts_contact.id(+);
                  The (+) outer joins the fts_contact table (what you called left join).

                  Message was edited by:
                  Jan-Marcel

                  Scott, How do you manage to format your postings?

                  Message was edited by:
                  Jan-Marcel
                  • 6. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                    264787
                    Thanks to you both you Scott and Jan for this, the combination of your posts sorted this out for me. I wasn't aware of the (+) syntax for left (outer) joins. Is this Oracle specific or have I been struggling with complex left join syntax needlessly for a long time (sql server, mysql etc.)??? ;-(

                    Jan, to format postings use
                    [ code ]
                    CREATE MATERIALIZED VIEW EXHIBITS_MVIEW
                    REFRESH ON COMMIT
                    ENABLE QUERY REWRITE
                    AS
                    SELECT fts_exhibit.id AS exhibit_id,
                    fts_exhibit.exhibit_ref,
                    fts_exhibit.case_ref, etc. etc. [ /code ]

                    without the spaces in the 'code' tags
                    CREATE MATERIALIZED VIEW EXHIBITS_MVIEW
                    REFRESH ON COMMIT
                    ENABLE QUERY REWRITE
                    AS
                    SELECT fts_exhibit.id AS exhibit_id,
                           fts_exhibit.exhibit_ref,
                           fts_exhibit.case_ref, 
                    Thanks again
                    Conrad

                    Message was edited by:
                    conrad.crampton
                    • 7. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                      27876
                      To keep the formatting of the posted code fragments, enclose the code between the [ pre] and [ /pre] tags (without the space after [ ).                                                                                                                                                                                                                                                                                           
                      • 8. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                        Jan-Marcel
                        Thanks for the formating tip.

                        I'm not that familiar with SQL server and mysql. At least the (+) syntax works for Oracle.
                        Left join, right join, just put the (+) on the (if any) joining column.
                        SELECT fts_exhibit.id AS exhibit_id,
                               fts_exhibit.exhibit_ref,
                               fts_exhibit.case_ref,
                               exhibit_item.ID AS exhibit_item_id,
                               exhibit_item.name,
                               exhibit_item.telephone_number,
                               exhibit_item.txt_message,
                               fts_contact.contact_name
                          FROM exhibit_item, 
                               fts_exhibit, 
                               fts_contact
                         WHERE fts_exhibit.id = exhibit_item.exhibit_id
                           AND fts_exhibit.contact_id = fts_contact.id(+);
                        is equal to
                        SELECT fts_exhibit.id AS exhibit_id,
                               fts_exhibit.exhibit_ref,
                               fts_exhibit.case_ref,
                               exhibit_item.ID AS exhibit_item_id,
                               exhibit_item.name,
                               exhibit_item.telephone_number,
                               exhibit_item.txt_message,
                               fts_contact.contact_name
                          FROM exhibit_item, 
                               fts_exhibit, 
                               fts_contact
                         WHERE fts_exhibit.id = exhibit_item.exhibit_id
                           AND fts_contact.id(+) = fts_exhibit.contact_id;
                        • 9. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                          264787
                          Thanks, you have been a great help
                          Conrad
                          • 10. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                            245482
                            I was merely suggesting that using one join syntax or the other -- instead of a combination of both might make the problem go away. Oracle has only implemented ANSI join syntax comparatively recently.
                            SELECT fts_exhibit.ID AS exhibit_id,
                                   fts_exhibit.exhibit_ref,
                                   fts_exhibit.case_ref,
                                   exhibit_item.ID AS exhibit_item_id,
                                   exhibit_item.NAME,
                                   exhibit_item.telephone_number,
                                   exhibit_item.txt_message,
                                   fts_contact.contact_name
                              FROM exhibit_item, fts_exhibit, fts_contact
                             WHERE fts_exhibit.ID = exhibit_item.exhibit_id
                               AND fts_contact.ID(+) = fts_exhibit.contact_id;
                            
                            SELECT fts_exhibit.id AS exhibit_id,
                                   fts_exhibit.exhibit_ref,
                                   fts_exhibit.case_ref,
                                   exhibit_item.id AS exhibit_item_id,
                                   exhibit_item.name,
                                   exhibit_item.telephone_number,
                                   exhibit_item.txt_message,
                                   fts_contact.contact_name
                              FROM exhibit_item
                                   JOIN fts_exhibit ON fts_exhibit.id = exhibit_item.exhibit_id
                                   LEFT OUTER JOIN fts_contact ON fts_contact.id = fts_exhibit.contact_id;
                            • 11. Re: Problems with Materialized view  - ORA-00907: missing right parenthesis
                              245482
                              Jan-Marcel,

                              I'm glad someone in your timezone answered your formatting question before I woke up here in Las Vegas.

                              Scott