Creating index over wildcard join on volatile table creation

Database

Creating index over wildcard join on volatile table creation

Hi!. I have 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;

Which throws the following error when run:

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

Creating the index as so.id or pu.id gives the following error:

[TeraJDBC 15.00.00.20] [Error 3706] [SQLState 42000] Syntax error: expected something between the word 'so' and '.'. 

Is there any way to either disambiguate the name, or remove the duplicate id column without having to name every column?.

Thanks,

Julián.

1 REPLY
Enthusiast

Re: Creating index over wildcard join on volatile table creation

Julian,

You will need to list the columns and give Id an alias(and any other columnnames that exist in both tables) for one of the tables

to avoid duplicate columnnames in your volatile table. The alias name for Id can then be used as Primary index.

Rglass