6

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?

10

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

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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