CODEWITHSUNDEEP
sqloracle10g
Chris Park
Chris Park

Reputation: 15

Oracle SQL *plus EXCEPT ORA-00907: missing right parenthesis

i wrote this query, but it gives me an error:

select c.sid
from catalog c
where exists ((select p.color from parts p)
except (select p2.color from parts p2 where p2.color != 'Red'));

ORA-00907: missing right parenthesis

can you tell me where is incorrect?

catalog: 

   SID        PID       COST

     1          3         .5
     1          4         .5
     1          8       11.7
     2          3        .55
     2          8       7.95
     2          1       16.5
     3          8       12.5
     3          9          1
     4          5        2.2
     4          6 1247548.23
     4          7 1247548.23

parts: 

   PID PNAME                               COLOR

     1 Left Handed Bacon Stretcher Cover   Red
     2 Smoke Shifter End                   Black
     3 Acme Widget Washer                  Red
     4 Acme Widget Washer                  Silver
     5 I Brake for Crop Circles Sticker    Translucent
     6 Anti-Gravity Turbine Generator      Cyan
     7 Anti-Gravity Turbine Generator      Magenta
     8 Fire Hydrant Cap                    Red
     9 7 Segment Display                   Green

Upvotes: 0

Views: 1250

Answers (1)

ruakh
ruakh

Reputation: 183602

i want to know sid of suppliers who supply only red parts

You can write:

SELECT c.sid
  FROM catalog c
  JOIN parts p
    ON p.pid = c.pid
 WHERE p.color = 'Red'
MINUS
SELECT c.sid
  FROM catalog c
  JOIN parts p
    ON p.pid = c.pid
 WHERE p.color <> 'Red'
;

The part before the MINUS finds all the suppliers who supply red parts; the part after the MINUS finds all the suppliers who supply non-red parts; so, the whole query finds all distinct suppliers who supply only red parts.

Upvotes: 1

Related Questions