Oracle renames columns from auto select?

Question

I have 2 tables with the following fields.

Table1

• AA
• BB
• CC
• DD

Table2

• AA
• CC
• Ee

Request

Select t1.*, t2.* from table1 t1, join table2 t2 on table1.DD = table2.EE 

My data columns are returned with the following column names:

 AA, BB, CC, DD, **AA_1**, **CC_1**, EE 

I don't need column names. I want them to have a table name prefixed in the names of the 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 this means that every choice around the world is getting 500 lines longer. Is there a magical way to do this in oracle? Basically, I want to write my code, for example

  select t1.* as t1_*, t2.* as t2_* from table1 t1, inner join table2 t2 on table1.DD = table2.EE 

But of course, this is not true SQL

Answer 1

There is currently no way in Oracle SELECT syntax to assign column aliases to multiple columns based on some expression. You must assign an alias for each individual column.

Answer 2

Is there a magic way to do this in oracle?

Not that I knew. Your options:

• Refer to the column naming scheme - you will need to use ALTER TABLE statements, for example:

   ALTER TABLE table_name RENAME COLUMN old_name to new_name; 

• Use column aliases

You can use views to save work and effort for defining column aliases, but this is not recommended due to poor performance when breaking views into each other.

Answer 3

Does the view create a parameter?

What software do you use that does this with you? I do not see this behavior in SQL * Plus or PL / SQL Developer in 10g. PL / SQL will not allow you to create a cursor with this ambiguity.

Answer 4

try it

  select t1.AA "t1_AA", t2.AA "t2.AA" from table1 t1, inner join table2 t2 on table1.DD = table2.EE 

As he said, you need to do it in a column