Search code examples
sqloracletable-aliasora-00936

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

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?

Solution

  • 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.