Re: More than 1 filed in Select *



I'm not sure why, but this is just the way Oracle forces you to implement this. If you want all columns from a table, and other values returned as well, you need to explicitly denote which table "*" refers to. In your example above, you use a table alias to denote which table's columns "*" refers to. But you could have used the table name as follows:

SQL> select 0,dual.* from dual;

0 D
---------- -
0 X

If I had to guess, I would say that in order to avoid any possible confusion, you must denote which table "*" refers to when you are using multiple tables, as can be seen below:

SQL> select * from dual,dual;
select * from dual,dual
*
ERROR at line 1:
ORA-00918: column ambiguously defined

Is the "*" above from the first table, the second table, or the combination of both? It's kind of ambiguous as the ORA-918 error tells us. So you have to denote which table, explicitly:

SQL> select d1.*, d2.* from dual d1, dual d2;

D D
- -
X X


HTH,
Brian




--
===================================================================

Brian Peasland
dba@xxxxxxxxxxxxxxxxxxx
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown
.

Relevant Pages

  • Art of Computer Programming problem
    ... Note that by << a denote a symbol used in the book, which refers to ... some relation on the set S. Describing the exact properties of << is ... It looks to me as a very badly-worded problem, ...
    (sci.math)
  • Replace vbCrLf
    ... We have an asp.net page that displays data from a SQL table that is updated ... That application uses vbCrLf to denote line breaks. ... David ...
    (microsoft.public.dotnet.framework.aspnet)
  • Comments in Access SQL
    ... Is it possible to build a commentry section into the SQL in a query similar ... to using a ' in Visual Basic to denote a comment? ...
    (microsoft.public.access.queries)