CODEWITHSUNDEEP
sqloracle-database
weexpectedTHIS
weexpectedTHIS

Reputation: 3376

Why am I getting "missing right parenthesis" for this query?

This query seems completely legit, I don't understand why I'm getting this error:

SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"

Query:

UPDATE "PROPS"
SET "PROPS"."FLAGGED" = NULL
WHERE "PROPS"."ID"   IN
  (SELECT "PROPS"."ID"
  FROM "PROPS"
  INNER JOIN "PINS"
  ON "PROPS"."PIN_ID"            = "PINS"."ID"
  WHERE "PINS"."CIRCUIT_SPEC_ID" = 10248
  AND "PROPS"."FLAGGED"          = 'WARN'
  ORDER BY pins.pin_type_id ASC);

Upvotes: 1

Views: 204

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Oracle doesn't always allow order by in subqueries. It is allowed in subqueries in the from clause. Otherwise, using rownum to get the first value doesn't work. Apparently, it doesn't work in the in clause.

Sadly, this is very poorly documented. On the page documenting in, it just specifies that a subquery is allowed. On the page documenting select, subqueries would seem to be allowed to have an order by clause. I am not sure if this is an error in the documentation or that I am misreading it. In any case, the order by in this context wouldn't really be doing anything.

Upvotes: 4

Related Questions