Re: [student] Nested SELECT statement. Going insane :(

From: Nuno Guerreiro <nuno-v-guerreiro_at_invalid.telecom.pt>
Date: Fri, 23 Oct 1998 14:13:31 GMT
Message-ID: <36318e1c.105389992@news.telecom.pt>

On Fri, 23 Oct 1998 12:34:12 GMT, ojw_at_iinet.net.au (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?
>
>I have RTFM that I have, but these are sadly :~( lacking.
>
>Your help anticipated with anxious glee. :)
>--
>On the smeeter.
>MHM 22x9
>--
>

Oops, looks like I've missed your point. You must omit the VALUES keyword, but you'll have to transform your SELECT statement in a radical way.

INSERT INTO FILM_HIRE VALUES
(SELECT a.film_no, b.cinema_code, '1-Aug-98','14-Aug-98',null FROM film a, cinema b
WHERE a.cinema_code=b.cinema_code)

/* not sure about the WHERE clause, it depends on your tables */

Hope this helps,

Nuno Guerreiro

---

As I don't normally appreciate unsolicited commercial e-mail (widely known as SPAM), I encoded my e-mail address. If you want to reply by e-mail, please remove the text added to fool spam software

"The art of arts, the glory of expression and the sunshine that lights the light of letters  is simplicity"

                                                        Walt Whitman Received on Fri Oct 23 1998 - 09:13:31 CDT