3 Replies Latest reply on May 1, 2013 8:01 AM by bencol

    syntax error: SQL Error: ORA-00907: missing right parenthesis

    1006212
      Hello dear friends

      I have a query in Oracle 11g, and i get this error :
      SQL Error: ORA-00907: missing right parenthesis

      SELECT F.CountryOfInterest, SUM(F.PopulationNum) as NeighborTotal from  
      (SELECT CB.CountryOfInterest, CB.BorderCountry, C.PopulationNum from 
           (SELECT Country1 as CountryOfInterest, Country2 as BorderCountry from Borders
       UNION
           SELECT Country2 as CountryOfInterest, Country1 as BorderCountry from Borders)
        as CB,Country C where CB.BorderCountry = C.Name)
        as F group by F.CountryOfInterest;
      what is the problem?



      thank you in advance.

      best
        • 1. Re: syntax error: SQL Error: ORA-00907: missing right parenthesis
          AlbertoFaenza
          Hi,

          remove AS before cb and f:
          SELECT f.countryofinterest, SUM (f.populationnum) AS neighbortotal
            FROM (SELECT cb.countryofinterest, cb.bordercountry, c.populationnum
                    FROM (SELECT country1 AS countryofinterest
                               , country2 AS bordercountry
                            FROM borders
                          UNION
                          SELECT country2 AS countryofinterest
                               , country1 AS bordercountry
                            FROM borders) cb
                       , country c
                   WHERE cb.bordercountry = c.name) f
           GROUP BY f.countryofinterest;
          "AS" is optional before column aliases, but not allowed before table aliases.

          Regards.
          Al

          Edited by: Alberto Faenza on May 1, 2013 10:02 AM
          comment added
          • 2. Re: syntax error: SQL Error: ORA-00907: missing right parenthesis
            padders
            I don't think using 'AS' for table alias is supported in Oracle (some vendors may support this, is this migrated code?)

            Try...
            SELECT   f.countryofinterest, SUM (f.populationnum) AS neighbortotal
            FROM     (SELECT cb.countryofinterest, cb.bordercountry, c.populationnum
                      FROM   (SELECT country1 AS countryofinterest,
                                     country2 AS bordercountry
                              FROM   borders
                              UNION
                              SELECT country2 AS countryofinterest,
                                     country1 AS bordercountry
                              FROM   borders) cb,
                             country c
                      WHERE  cb.bordercountry = c.name) f
            GROUP BY f.countryofinterest;
            • 3. Re: syntax error: SQL Error: ORA-00907: missing right parenthesis
              bencol
              You cannot use "as" to specifiy aliases of tables:
              SQL> select a.* from dual a;
              
              D
              _
              X
              works, but
              SQL> select a.* from dual as  a;
              select a.* from dual as  a
                                   *
              ERROR at line 1:
              ORA-00933: SQL command not properly ended
              does not.

              "as" only works for column aliases