All Forums Database
jbayardo 1 post Joined 03/15
08 Apr 2015
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.

Glass 225 posts Joined 04/10
08 Apr 2015

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 

You must sign in to leave a comment.