I have 2 tables with the following fields.
Query
Select t1.*,
t2.*
from table1 t1,
join table2 t2 on table1.DD = table2.EE
My data columns back with the following column names:
AA, BB, CC, DD, **AA_1**, **CC_1**, EE
I don't want the column names like that. I want them to have the table name prefixed in the names of common (or all columns). I could fix this with:
select t1.AA as t1_AA, t1.BB as t1_BB, t1.CC as t1_CC, t1.DD as t1_DD,
t2.AA as t2_AA, t2.CC as t2_CC, t2.EE as t2_EEE
from table1 t1,
inner join table2 t2
on table1.DD = table2.EE
But that means every select everywhere becomes 500 lines longer. Is there a magic way to do this in oracle? Basically I want to write my code like
select t1.* as t1_*, t2.* as t2_*
from table1 t1,
inner join table2 t2
on table1.DD = table2.EE
But of course that is not valid SQL
Is there a magic way to do this in oracle?
Not that I'm aware of. Your options amount to:
Address the column naming scheme - you'd need to use ALTER TABLE statements like:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
Use column aliases
You could use views to save on the work & effort of defining column aliases, but it's not a recommended practice because of the bad performance when layering views on top of one another.