Update query with not in statement Oracle

Question

I am running an update statement with a NOT IN which contains another query which return me some visa numbers.

There seems too be an error which i cannot trace it, i doubt if i can use a query in NOT IN.. if some one could point out what i am doing wrong here.

Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:    
*Action:





UPDATE HUDHAIFA.VISA_APP
    SET VISA_NO=(LPAD(TRIM(VISA_NO),8,'0'))
    WHERE LENGTH(VISA_NO) < 8
    AND VISA_NO IS NOT NULL
    AND VISA_NO NOT IN (  
      SELECT
      CASE
        WHEN LTRIM(VISA_NO, '0') IS NULL THEN VISA_NO
        WHEN LTRIM(VISA_NO, '0') IS NOT NULL THEN LTRIM(VISA_NO, '0') 
      END as VISA_NO
      FROM HUDHAIFA.VISA_APP
      WHERE LENGTH(VISA_NO) < 8
      GROUP BY CASE
        WHEN LTRIM(VISA_NO, '0') IS NULL THEN VISA_NO
        WHEN LTRIM(VISA_NO, '0') IS NOT NULL THEN LTRIM(VISA_NO, '0') 
      END
      HAVING COUNT(*) > 1
      ORDER BY VISA_NO asc
    );

score 0 · Accepted Answer · answered Dec 20 '17 at 07:11

0

Please try removing ORDER BY VISA_NO asc and run. It is syntactically incorrect and not required in your case.

answered Dec 20 '17 at 07:11

Bikash Ranjan Bhoi

518
2
10

score 0 · Answer 2 · answered Dec 20 '17 at 07:37

Generally, ORA-907 usually doesn't concert right parenthesis but means something like 'there is syntax error near this place so I cannot imagine what you want and guess you should close left parenthesis opened somewhere before'. In your case, as Bikash Ranjan Bhoi said, reason is ORDER BY clause which is senseless into such subquery.

You should also check that this subquery will never produce NULL among it's results. Both IN and NOT IN statements works requires not-null sets of values.

Update query with not in statement Oracle

2 Answers2