CODEWITHSUNDEEP
sqloracle-database
cjs
cjs

Reputation: 320

SQL command not properly terminated on select statement in multiple line query

When creating temporary tables in an SQL query I keep getting an error that says my SELECT command is not properly ended. The actual query I'm trying to write lists the Library Branch ID and number of copies of "The Innovator's Way" in that library.

Table BOOK_COPIES has foreign keys Branch_id and Book_id that relate to the tables LIBRARY_BRANCH and BOOK, respectively. Title is an attribute of BOOK.

The query I wrote is:

SQL> SELECT Book_id , Branch_id , No_of_copies
2  INTO COPIES_AT_BRANCH
3  FROM BOOK_COPIES , LIBRARY_BRANCH
4  GROUP BY Branch_id
5  SELECT Title , Branch_id , No_of_copies
6  INTO BOOK_NO
7  FROM COPIES_AT_BRANCH , BOOK
8  GROUP BY Book_id
9  SELECT Branch_id , No_of_copies
10 FROM BOOK_NO
11 WHERE Title = 'The Innovator''s Way';

The error I am getting is:

SELECT Title , Branch_id , No_of_copies
*
ERROR at line 5:
ORA-00933: SQL command not properly ended

I am very new to SQL and databases in general so the my mistake might be incredibly simple. Also, the query might not be correct in general.

Any help is appreciated, thanks.

Upvotes: 1

Views: 1709

Answers (2)

sstan
sstan

Reputation: 36483

Apart from the semi colon problem, you will find that your statements will fail because of the INTO clause of your first 2 statements.

In Oracle, INTO is valid in a SQL query when writing PL/SQL code. But based on your comments, it sounds like what you are actually trying to do is create a table based on the results of a select statement. That syntax may work in other databases, like SQL Server, but it doesn't work in Oracle.

The syntax you'll want to use is:

create table table_name as select ...

Also, you'll want to have a look at how you are joining your tables in your FROM clause. Not only are you using an older syntax for performing the join (look into ANSI JOIN syntax instead), but you also appear to be performing a cross join, which will give you the cartesian product of your 2 tables. This is rarely a good thing. You may want to look into that.

Upvotes: 1

leeor
leeor

Reputation: 17771

You have multiple SELECT queries which you are not ending with a semi-colon ;. Try this:

SQL> SELECT Book_id , Branch_id , No_of_copies
2  INTO COPIES_AT_BRANCH
3  FROM BOOK_COPIES , LIBRARY_BRANCH
4  GROUP BY Branch_id;
5  SELECT Title , Branch_id , No_of_copies
6  INTO BOOK_NO
7  FROM COPIES_AT_BRANCH , BOOK
8  GROUP BY Book_id;
9  SELECT Branch_id , No_of_copies
10 FROM BOOK_NO
11 WHERE Title = 'The Innovator''s Way';

Upvotes: 3

Related Questions