CODEWITHSUNDEEP
sqloracle-database
user1378863
user1378863

Reputation: 189

ORA-00907: missing right parenthesis

I can't seem to figure out why this error is occurring.

Here is my code:

SELECT DISTINCT c.cid 
  FROM customers c
 WHERE EXISTS ( SELECT DISTINCT t.aid 
                  FROM transactions t
                 WHERE EXISTS ( SELECT a.balance 
                                  FROM accounts a 
                                 WHERE t.cid = c.cid
                                   AND a.aid = t.aid
                                   AND ((sysdate - a.date_opened) > 365)
                                 ORDER BY a.balance DESC
                               )
              );

The error is in the last line.

Upvotes: 2

Views: 1687

Answers (3)

abhishek
abhishek

Reputation: 37

you are doing mistake in AND ((sysdate - a.date_opened) > 365)

use like this :-

AND (sysdate - a.date_opened) > 365

Upvotes: 0

Andy Lester
Andy Lester

Reputation: 93676

Side note: Your subquery is going to require a full table scan on accounts, even if you have an index on date_opened, because you are doing math in the WHERE clause.

For every row, Oracle has to evaluate this expression:

((sysdate - a.date_opened) > 365)

to take the value of sysdate and subtract the date opened and then compare it to 365. It has to check each row individually.

However, if you algebraically change that expression to

sysdate - 365 > a.date_opened

then it only has to evaluate the expression sysdate-365 once, at the beginning of the query, and can then compare that to a.date_opened and use the index for an index scan.

Upvotes: 0

Pavel
Pavel

Reputation: 151

There are 2 issues:

  1. You can't reference table from subquery which is not a direct parent level — a subquery inside EXISTS does not see a table c, so condition t.cid = c.cid is wrong
  2. I don't see any meaning in ORDER BY inside EXISTS subquery. It also can be illegal, but I'm not sure.

I advise you to rewrite query from 2 EXISTS into 2 JOINS. It would be like:

SELECT cid 
  FROM (SELECT c.cid, a.balance
          FROM customers c,
               accounts a,
               transactions t 
         WHERE t.cid = c.cid
           AND a.aid = t.aid
           AND sysdate - a.date_opened > 365)
 ORDER BY balance DESC

Upvotes: 1

Related Questions