I've encountered an issue (or non-issue?) and would need some clarification:
Here's how to simulate it
Database: Vertica 7
create table parent
(
parent_id int,
description varchar(20),
child01 number,
child02 number
);
create table child
(
child_id int,
child_description varchar(20)
);
insert into parent values (1,'Parent 01',100,200);
insert into child values (100, '1hundred');
insert into child values (200, '2hundred');
commit;
=> select * from parent;I'm trying to connect the parent table with the child table (for each of its values):
parent_id | description | child01 | child02
-------------+---------------+----------+---------
1 | Parent 01 | 100 | 200
=> select * from child;
child_id | child_description
-----------+-------------------
100 | 1hundred
200 | 2hundred
=> selectAs you can see, the SELECT works. But when I try and create a projection using the same query, it fails:
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
-> (select child_id first_child_id, child_description from child) first_child,
-> (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;
parent_id | first_child_description | second_child_description
-------------+-----------------------------+--------------------------
1 | 1hundred | 2hundred
=> create projection parent_child_projectionI can create a view using the same query though:
-> as
-> select
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
-> (select child_id first_child_id, child_description from child) first_child,
-> (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;
ERROR 3146: Duplicate columns in select list of projection not allowed
=> create view parent_child_viewAm I doing something wrong here? Hope you guys can help.
-> as
-> select
-> parent.parent_id,
-> first_child.child_description first_child_description,
-> second_child.child_description second_child_description
-> from parent,
-> (select child_id first_child_id, child_description from child) first_child,
-> (select child_id second_child_id, child_description from child) second_child
-> where parent.child01 = first_child.first_child_id
-> and parent.child02 = second_child.second_child_id;
CREATE VIEW
=> select * from parent_child_view;
parent_id | first_child_description | second_child_description
-------------+-----------------------------+--------------------------
1 | 1hundred | 2hundred
Thanks!
Ian
