Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

SQL Server allows to use SELECT statements similar to:

SELECT FirstName, LastName, City, *
FROM dbo.Customers

When I try to execute such a query on Oracle database, I get the following error:

ORA-00936: missing expression
00936. 00000 - "missing expression"

Doesn't Oracle support such queries?

share|improve this question
add comment

2 Answers

up vote 6 down vote accepted

Only if you prefix the asterisk with the table name:

select dummy, dual.* from dual;

DUMMY DUMMY 
----- ----- 
X     X     

Which limits it's use to tables not joined with the using syntax:

select dummy, d1.* from dual d1 join dual d2 using(dummy)

SQL Error: ORA-25154: column part of USING clause cannot have qualifier
25154. 00000 -  "column part of USING clause cannot have qualifier"
*Cause:    Columns that are used for a named-join (either a NATURAL join
           or a join with a USING clause) cannot have an explicit qualifier.
*Action:   Remove the qualifier.

select d2.dummy, d1.* from dual d1 join dual d2 on(d1.dummy=d2.dummy);

DUMMY DUMMY 
----- ----- 
X     X     
share|improve this answer
    
So when I am joining multiple tables, I need to use FirstName, LastName, City, Customers.*, Orders.*, right? –  Marek Grzenkowicz May 30 '11 at 10:16
    
right - and don't use using –  Jack Douglas May 30 '11 at 10:18
add comment

Instead of table names you can use aliases

SELECT FirstName, LastName, City, c.*
FROM Customers c;

Even on SQL server 2008 you get into trouble, when your query evolves

SELECT FirstName, LastName, City, *
FROM Customers 
ORDER BY Firstname ;

you will have to change it to

SELECT FirstName, LastName, City, c.*
FROM Customers c 
ORDER BY c.Firstname ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.