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_at_nospam.peasland.net
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