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?

Your service should have a FacilityID, which is a foreign key to the Primary Key on the Facilities table. The Facility table should not have a reference to the Service table. You then need to create a service record for each service at a facility. So if 2 facilities offer water, then you would have 2 records in service, one for Water and Facility 1, one for Water and Facility 2 — Stefan H, May 03 '12 at 22:49
@StefanH I don't know if you understand what I am trying to do. I have a facility say `Health Centre`. This Health centre can have a `Gym`, `Pool` and `Spa`. However I don't want the `Gym` to be available to any other facility — adohertyd, May 03 '12 at 22:50
Yes, your table structure is correct, but are you adding records to it correctly? — Stefan H, May 03 '12 at 22:51

Justin Cave · Accepted Answer · 2012-05-03T23:10:47.517

8

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.

edited May 03 '12 at 23:10

answered May 03 '12 at 22:53

Justin Cave

227,342
24
367
384

@adohertyd - Did you intend to post an `INSERT` statement? – Justin Cave May 03 '12 at 22:56
@adohertyd - please post that sql in your question so that we can address your real problem. – Stefan H May 03 '12 at 23:00
The reason I select from Dual is because that was the only syntax I found for this particular type of `INSERT` statement. I have a vague idea of how the the Dual table works but I am open to improving my coding standard. Is it just a matter or removing the dual statement and formatting as per Stefan's post? – adohertyd May 03 '12 at 23:16
@adohertyd - It's just a matter of following Stephan's syntax yes. In general, if you are trying to insert data from table A (`Facilities`) into table B (`Services`), you want to structure the statement to `SELECT` from A. If you are trying to insert a bunch of literals, you could select from dual (which is just a table that has exactly 1 row at all times) or you could use an `INSERT ... VALUES`. – Justin Cave May 03 '12 at 23:26

score 3 · Answer 2 · answered May 03 '12 at 23:02

3

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.

answered May 03 '12 at 23:02

Stefan H

6,635
4
24
35

Is this the correct syntax for this type of input all the time? I have a number of `INSERT` statements for other tables that are the same syntax as the one I posted and they work fine. I'm new to SQL and struggling with some of these syntax's – adohertyd May 03 '12 at 23:06

Syntax error: missing expression (ORA-00936)

2 Answers2