Oracle Missing Right Parenthesis

Question

Here is my query:

SELECT * FROM Auta WHERE SUBSTR(spz, 1, 2) = 
(SELECT SUBSTR(spz, 1, 2) FROM Auta WHERE typ = 'BMW' AND specifikacia_typu = 'Z1' LIMIT 1);

And when I run it I get this error:

ORA-00907: missing right parenthesis

I'm getting a little desperate, I've already tried adding parentheses everywhere in the query and I still get the error? There are 3 left and 3 right parentheses in the query so everything should be all right.

Answer 1

Hi Richard,

The LIMIT clause doesn't exist in Oracle. Instead you would use rownum:

SELECT *
  FROM Auta
 WHERE SUBSTR(spz, 1, 2) = (SELECT SUBSTR(spz, 1, 2)
                              FROM Auta
                             WHERE typ = 'BMW'
                               AND specifikacia_typu = 'Z1'
                               AND ROWNUM = 1);

Answer 2

What's that "LIMIT 1" for? I don't believe that's correct Oracle syntax. If you're trying to limit the output to one row, use:

WHERE rownum = 1

Answer 3

LIMIT 1 is a MySQLism, as far as I know.

  SELECT *
    FROM Auta
   WHERE SUBSTR(spz, 1, 2) =
         (SELECT SUBSTR(spz, 1, 2)
          FROM Auta
         WHERE typ = 'BMW'
           AND specifikacia_typu = 'Z1'
           AND ROWNUM = 1);

...should give you the expected results