CODEWITHSUNDEEP
sqlteradata
Misguided
Misguided

Reputation: 1302

Joining tables using wildcard and avoiding repeated columns

I'm trying to run the following query

create multiset volatile table newTable, no log as (
SELECT so.*, pu.*
FROM db.table1 so
left outer join db.table2 pu on so.id = pu.id
) with data primary index(id) on commit preserve rows;

Teradata is giving me the following error:

[TeraJDBC 15.00.00.20] [Error 3809] [SQLState 42S02] Column 'id' is ambiguous.

Because of the index I want to create over the ID column. My question is, then, is there any way to either avoid having the joining column duplicate or to disambiguate the index?

Upvotes: 0

Views: 943

Answers (2)

dnoeth
dnoeth

Reputation: 60462

This is due to the so.*, pu.*, both tables got a column with the name id and thus you try to create a table with the same column name twice.

Change the * to the list of columns and either exclude id or add an alias, e.g. so.id as so_id

Upvotes: 1

Russ
Russ

Reputation: 4163

This line:

with data primary index(id)

Which table is "id" here supposed to be from? This is where your error is.

Upvotes: 0

Related Questions