I've encountered an issue (or non-issue?) and would need some clarification:
Here's how to simulate it
Database: Vertica 7 (but I think same behaviour applies to other versions)
Code: Select all
create table parent
(
parent_id int,
description varchar(20),
child01 number,
child02 number
);
create table child
(
child_id int,
child_description varchar(20)
);
Code: Select all
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;
parent_id | description | child01 | child02
-------------+---------------+----------+---------
1 | Parent 01 | 100 | 200
=> select * from child;
child_id | child_description
-----------+-------------------
100 | 1hundred
200 | 2hundred
Code: Select all
=> 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;
parent_id | first_child_description | second_child_description
-------------+-----------------------------+--------------------------
1 | 1hundred | 2hundred
Code: Select all
=> create projection parent_child_projection
-> 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
Code: Select all
=> create view parent_child_view
-> 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