Oracle SQL - nested subquery throws parenthesis exception

Question

I got the following oracle-sql-statement:

select xxx.a, xxx.b, xxx.c, xxx.d,
(SELECT e FROM ghj WHERE f in (SELECT u FROM hgz WHERE ddf = xxx.ddf limit 
1)) AS fff
from fcv xxx where k = '100';

I get the following error: ORA-00907: missing right parenthesis ORA-06512: at "SYS.DBMS_SQL", line 1185 ORA-06512: at line 1

Why?

`limit 1` isn't Oracle syntax. (It's expecting a closing parenthesis when it sees `limit`; not an entirely helpful error though). — Alex Poole, Jul 25 '18 at 10:40
a useful [link](https://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering) — Aleksej, Jul 25 '18 at 10:41

score 0 · Accepted Answer · edited Jul 25 '18 at 11:17

0

With respect of the comments remove the limit as its not oracle syntax

select xxx.a,
       xxx.b,
       xxx.c,
       xxx.d,
       (SELECT e FROM ghj WHERE f in (SELECT u FROM hgz WHERE ddf = xxx.ddf 
       FETCH FIRST 1 ROWS ONLY) FETCH FIRST 1 ROWS ONLY) AS fff
  from fcv xxx
 where k = '100';

edited Jul 25 '18 at 11:17

Madness

125
1
10

answered Jul 25 '18 at 11:05

Moudiz

7,211
22
78
156

nearly correct. I had to add a limitation with correct oracle sql syntax though, because the subquery used right of the "in" has to return a maximum of one entry, or the query is flawed again. – Madness Jul 25 '18 at 11:11
well if you want you can use `rownum` ... and rownum=1 @Madness – Moudiz Jul 25 '18 at 11:15
ahh thank you! good idea – Madness Jul 25 '18 at 12:57

Oracle SQL - nested subquery throws parenthesis exception

1 Answers1