Why is selecting specified columns, and all, wrong in Oracle SQL?

sql oracle table-alias ora-00936
glasnt picture glasnt · Feb 23, 2010 · Viewed 15.1k times · Source

Say I have a select statement that goes..

select * from animals

That gives a a query result of all the columns in the table.

Now, if the 42nd column of the table animals is is_parent, and I want to return that in my results, just after gender, so I can see it more easily. But I also want all the other columns.

select is_parent, * from animals

This returns ORA-00936: missing expression.

The same statement will work fine in Sybase, and I know that you need to add a table alias to the animals table to get it to work ( select is_parent, a.* from animals ani), but why must Oracle need a table alias to be able to work out the select?

Answer

Jim Hudson picture Jim Hudson · Feb 23, 2010

Actually, it's easy to solve the original problem. You just have to qualify the *.

select is_parent, animals.* from animals;

should work just fine. Aliases for the table names also work.

Related questions

Is there a way to give a subquery an alias in Oracle 11g SQL?

Is there a way to give a subquery in Oracle 11g an alias like: select * from (select client_ref_id, request from some_table where message_type = 1) abc, (select client_ref_id, response from some_table where message_type = 2) defg …

Read More
oracle: can you assign an alias to the from clause?

can you assign an alias to the from clause? like: select a - b "Markup" from retail a, cost b; EDIT: sorry i typed that out a bit too quick and tried to simplify the question to the point where …

Read More
How to use the 'as' keyword to alias a table in Oracle?

I'm trying to execute this query in Oracle SQL Developer: SELECT G.Guest_ID, G.First_Name, G.Last_Name FROM Guest AS G JOIN Stay AS S ON G.Guest_ID = S.Guest_ID WHERE G.City = 'Miami' AND …

Read More