Duplicate columns in outer join

[pbibler]

I have several tables which I want to join into one very large table (~950 columns). These tables all have a key to link them, but that key has the same name in all tables. So when I type in:

select a.*, b.*, c.*

from a left outer join b left outer join c
(I've simplified to save space)

I get a duplicate column error--the key column. What I'd like to do is to join every column but one from b and c to a. Is this possible without actually writing out each column name? This is something I'd like to be independent of table definitions. Sorry for the long post!

Thanks,

Paul

 

[BillDHS]

I would think that you could use DBC view DBC.Columns to create a derived table to avoid typing all the column names.

select DatabaseName, TableName, ColumnName from dbc.columns
where tablename in (table 1, 2, 3)
and not tablename || columname = ( put the columns to avoid dups in this syntax)

 

[pbibler]

Thanks, BillDHS. I've tried to use this, but I'm not sure how I could do it. I can make a table with the column names, but how can I use that to select from and join my tables? I'm no expert on derived tables. Conceptually, I can see how you could do something like:

create table tab1 as (
select a.*, b.*, c.*

from a left outer join b left outer join c

where <tab1.columnname> not in (select columnname from dbc.columns where columnname='<dupe column>' and tablename='<tables with duped columns>'))

but I don't know how to use the column name of a table like I'm using <tab1.columnname> here. Am I on the wrong track?

Thanks for your help, you are most appreciated.

Paul

 

[BillDHS]

I played around with it a little bit, I'm not sure it will work. We are still on V2R3, does your version allow Select clause in the Select like some db's do?

Select (select colm1 from tab1), tab2.col2
from tab2

 

[pbibler]

I don't think so, I think I might be out of luck. Thanks for your help, though, you're great to try and help. I guess I'll have to come up with something else.

Paul