CODEWITHSUNDEEP
sqlselectplsqlwildcardto-char
Marc
Marc

Reputation: 435

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

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.

Upvotes: 3

Views: 951

Answers (5)

jag
jag

Reputation: 21

Rather than lecture you, here. Oracle is a little fussier than MSSQL about it, but this worked for me.

SELECT GENERAL.GOREMAL.* ,rownum ,current_date from GENERAL.GOREMAL

Upvotes: 2

HLGEM
HLGEM

Reputation: 96610

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.

Upvotes: 0

Joe Koberg
Joe Koberg

Reputation: 26729

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.

Upvotes: 1

TheTXI
TheTXI

Reputation: 37905

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;

Upvotes: 4

JoshBerke
JoshBerke

Reputation: 67108

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:-)

Upvotes: 0

Related Questions