Syntax error: missing expression (ORA-00936)

Question

I have 2 tables, Facilities and Services.

CREATE TABLE Facilities (
facility_id NUMBER(2) NOT NULL,
facility_name VARCHAR2(20) NOT NULL,
CONSTRAINT pk_facil_id PRIMARY KEY (facility_id)
);

CREATE TABLE Services (
service_id NUMBER(2) NOT NULL,
service_name VARCHAR(20) NOT NULL,
service_facility NUMBER(2) NOT NULL,
CONSTRAINT pk_serviceid PRIMARY KEY (service_id)
);

ALTER TABLE Services
add CONSTRAINT fk_servicefacility FOREIGN KEY(service_facility) 
REFERENCES Facilities(facility_id);

If I try entering records into the 'Services' table like so:

INSERT INTO Services (service_id, service_name, service_facility) 
SELECT 06, 'Rooms', 
(SELECT facility_id, FROM Facilities WHERE facility_name = 'Hotel') 
FROM Dual;

I get an error "missing expression" for 3 out of the 7 insert statements. What expression is missing?

Answer 1

The SQL statement you posted has an extra comma. If you run the statement in SQL*Plus, it will throw the ORA-00936: missing expression and show you exactly where the error occurs

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO Services (service_id, service_name, service_facility)
  2  SELECT 06, 'Rooms',
  3  (SELECT facility_id, FROM Facilities WHERE facility_name = 'Boston')
  4* FROM Dual
SQL> /
(SELECT facility_id, FROM Facilities WHERE facility_name = 'Boston')
                     *
ERROR at line 3:
ORA-00936: missing expression

If you remove the comma, the statement works

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO Services (service_id, service_name, service_facility)
  2  SELECT 06, 'Rooms',
  3  (SELECT facility_id FROM Facilities WHERE facility_name = 'Boston')
  4* FROM Dual
SQL> /

1 row created.

Note, however, that I would generally prefer Stefan's syntax where you are selecting from Facilities rather than selecting from dual with a scalar subquery.

Answer 2

Your insert statement should be:

INSERT INTO 
    Services 
    (
        service_id, 
        service_name, 
        service_facility
    ) 
SELECT 
    06, 
    'Rooms', 
    facility_id 
FROM 
    Facilities 
WHERE 
    facility_name = 'Hotel'

In it's current state the query i provided will add a service record for rooms for each facility with the name of Hotel.

And then add on a join to your 'Dual' table so that you get the correct number of inserts / are getting the correct facilities.