Oracle SQL - Prefix duplicate column names for 'create as select'

Question

I think what I am trying to achieve has sort of been asked before in another guise here. But I have a slightly different requirement and more restrictions on what I can do.

In this simplified scenario:

CREATE TABLE backup_data AS SELECT a.*, b.*, c.*
FROM tbl_a a, tbl_b b, tbl_c c
WHERE a.aid = b.bid
AND b.cid = c.cid;

tbl_a, tbl_b and tbl_c have some columns with the same name, which Oracle will return a duplicate column name error for when I try to run it.

What I am hoping for is that there is some way to distinguish the column names so that I can insert them despite having the same name.

I thought this might be possible with a concatenated alias something like:

CREATE TABLE backup_data AS SELECT a.* 'tbl_a_' || COLUMN_NAME, b.* 'tbl_b_' || COLUMN_NAME, c.* 'tbl_c_' || COLUMN_NAME
FROM tbl_a a, tbl_b b, tbl_c c
WHERE a.aid = b.bid
AND b.cid = c.cid;

I fear I already know the only solution will be either:

Suck it up and write the 617 column names out by hand with appropriate aliases OR
Use some awesome PLSQL

Well unfortunately I don't have the luxury of PLSQL in this particular instance nor do I really have the luxury of time to do it by hand.

Are there any other ways I can achieve this create as select with duplicate column names?

Are the columns with the same names the columns that you are joining on? Or are there additional columns that are not part of the join that have the same name? Generally, a SELECT statement can happily return multiple columns with the same name, Oracle can implicitly provide an appropriate alias for the second column. I assume that the requirement for unique aliases is coming from whatever you're doing with the results of your select. You're showing an insert as select which isn't valid SQL syntax-- perhaps you're really doing a CREATE TABLE AS SELECT? — Justin Cave
– Justin Cave, Commented May 27, 2014 at 23:20
Sorry you are right I am trying to create as select... I've refactored the OP. Is there a better way to do this the the ways I mentioned? — travega
– travega, Commented May 28, 2014 at 0:11
Are the columns with the same names the columns that you are joining on? Or are there additional columns that are not part of the join that have the same name? — Justin Cave
– Justin Cave, Commented May 28, 2014 at 1:04
Yes there are join columns with the same name as well as a vast number of non-join duplicate names. — travega
– travega, Commented May 28, 2014 at 1:36

MathCurious · Accepted Answer · 2014-05-28 03:37:51Z

Your options:

suck it up (you already correctly noted it!)
use pl/sql to dynamically generate the statement "interrogating" oracle system views.
use hybrid approach - generate STATIC statement (or list of the columns) DYNAMICALLY.

As example - I have output_file table. it has more than 100 columns.

select TABLE_NAME , LISTAGG(COLUMN_NAME||'1',',') WITHIN GROUP (ORDER BY COLUMN_NAME) AS COL_NAME from user_tab_cols where table_name = 'OUTPUT_FILE'
 GROUP BY TABLE_NAME;

items to pay attention: you noted that there are a lot of columns with the same name when "crossing" table, so when a list of columns is generated , I added "1", so next table will have "2" and so on. you also need to properly set the screen of your client to make sure that generated list is not truncated. on SQL+ it would be

COL COL_NAME FORM A400

(that is setting for my example) ASSUMPTION: listagg is available on 11g Rel 2. similar tricks can be used on 10g and 11.1. Let me know if need some help for older DB's.

Collectives™ on Stack Overflow

Oracle SQL - Prefix duplicate column names for 'create as select'

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related