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

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. Received on Fri Oct 23 1998 - 09:11:24 CDT