Select * from Table and still perform some function on a single named column

Question

I'd like to be able to return all columns in a table or in the resulting table of a join and still be able to transform a date to a string by name.

For example

Select ID, DESCRIPTION, TO_CHAR(CHANGE_DATE,'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;

This is all well and good for just these three columns. But, the table will actually have many more columns and may be joined onto other tables. I'd like to be able to use a wildcard to get all the columns and still be able to perform the TO_CHAR transformation.

Something like : SELECT *, (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE FROM MY_TABLE;

As you would have guessed from TO_CHAR, I am using an Oracle so I'm using PLSQL.

So my specific question is: Is there a syntax that would allow me to select all columns (via *) and still be able to call a function on single column within those columns.

Answer 1

In SQL Server what you wrote is perfectly valid, I'd assume it should work in Oracle as well. Just be aware you will be returning date column twice once in its orginal form and once in the Formated form.

FYI Using SELECT * should probally be avoided but that's for another question:-)

Answer 2

The closest you could do is something like:

SELECT 
     MY_TABLE.*, 
     (CHANGE_DATE, 'YYYY-MM-DD HH24:MI:SS') AS FORMATED_DATE 
FROM MY_TABLE;

Answer 3

The following is acceptable:

SELECT T1.*, T2.*, x + y as some_Z

or perhaps

SELECT compute_foo() as aColumn, *

The last of which will always keep your special columns in the same place - at the beginning - no matter how many columns the wildcard brings in.

Answer 4

FYI, if you have joins, select * is especially to be avoided as it wastes server and network resources especially since all join fields have the same information. Junk coding like "select *" creates performance problems that become very difficult to fix when every query in the system is poorly written. I know in SQL Server you can drag the columns over from the object browser, wouldn't be surprised if ORacle had something similar.

In addition select * can create many, many later bugs as the data tables change. It is poor practice to not name your columns in the specific order you want them.