4

I have 2 tables with the following fields.

Table1

  • AA
  • BB
  • CC
  • DD

Table2

  • AA
  • CC
  • EE

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

bwawok
  • 14,898
  • 7
  • 32
  • 43

4 Answers4

5

Is there a magic way to do this in oracle?

Not that I'm aware of. Your options amount to:

  1. 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;

  2. 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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
5

In Oracle SELECT syntax, there is currently no way to assign column aliases to multiple columns based on some expression. You have to assign an alias to each individual column.

vls
  • 2,304
  • 15
  • 20
0

Try this

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

As he said before, you need to do it per column

Badmiral
  • 1,549
  • 3
  • 35
  • 74
  • The OP already included that approach, in the 'I could fix this with' part... so I'm not sure what this adds. I don't see anything suggesting quoted identifiers are needed, apart from maybe `as t1.BB` which looks like a typo. – Alex Poole Feb 15 '13 at 16:55
0

Is creating a view an option?

What is the software you're using that does this to you? I don't see this behavior in SQL*Plus or PL/SQL Developer in 10g. PL/SQL won't let you build a cursor with this ambiguity in it.

DCookie
  • 42,630
  • 11
  • 83
  • 92