Xref: alice comp.databases.oracle.misc:21664
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!Supernews60!supernews.com!ip.att.net!attbt1!news.abbott.com!news@abbott.com
From: Graham Thornton <graham.thornton@ln.ssw.abbott.com.nospam>
Newsgroups: comp.databases.oracle.misc
Subject: Re: [student] Nested SELECT statement. Going insane :(
Date: Fri, 23 Oct 1998 09:11:24 -0500
Organization: Abbott Laboratories
Lines: 53
Message-ID: <36308E8C.2D08@ln.ssw.abbott.com.nospam>
References: <363076db.4425081@news.m.iinet.net.au>
Reply-To: graham.thornton@ln.ssw.abbott.com.nospam
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 3.01 (Win95; I)

Oliver White wrote:
> 
> INSERT INTO FILM_HIRE VALUES
>    ((SELECT FILM_NO
>         FROM FILM
>         WHERE FILM_NAME = 'Kundun'),
>     (SELECT CINEMA_CODE
>         FROM CINEMA
>         WHERE CINEMA_NAME = 'Rialto'),
>     '1-Aug-98', '14-Aug-98', NULL);
> 
> I have this code, it's obviously wrong by the notes I have don't
> explain this structure, could someone explain why I get the error:
> 
>    (SELECT FILM_NO
>     *
> ERROR at line 2:
> ORA-00936: missing expression
> 
> And give me an example of proper syntax?

There's a couple of problems here.....

Firstly, you simply cannot do a dual select operation like the one
above.

You need to do a single select such as....

SELECT FILM_NO, CINEMA_CODE, '1-Aug-98', '14-Aug-98',NULL
FROM FILM, CINEMA
WHERE FILE_NAME = 'Kundun'
AND CINEMA_NAME = 'Rialto'
AND <whatever joins the FILM table to the CINEMA table is true>;

Secondly, lose the VALUES keyword....

INSERT INTO FILE_HIRES ( 
SELECT FILM_NO, CINEMA_CODE, '1-Aug-98', '14-Aug-98',NULL
FROM FILM, CINEMA
WHERE FILE_NAME = 'Kundun'
AND CINEMA_NAME = 'Rialto'
AND <whatever joins the FILM table to the CINEMA table is true>
);

> 
> I have RTFM that I have, but these are sadly :~( lacking.
> 

Unfortunately that is often the case.

Good luck with your assignment

Graham.