I have the following two tables (ORDER_DETAIL and LU_EMPLOYEE)
Schemas of the two tables are mentioned below
Code: Select all
dbadmin=> \d superapp.order_detail
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+--------------+--------------+-------+------+---------+----------+-------------+-------------
superapp | ORDER_DETAIL | ORDER_ID | int | 8 | | f | f |
superapp | ORDER_DETAIL | ITEM_ID | int | 8 | | f | f |
superapp | ORDER_DETAIL | ORDER_DATE | date | 8 | | f | f |
superapp | ORDER_DETAIL | EMP_ID | int | 8 | | t | t |
superapp | ORDER_DETAIL | PROMOTION_ID | int | 8 | | f | f |
superapp | ORDER_DETAIL | QTY_SOLD | float | 8 | | f | f |
superapp | ORDER_DETAIL | UNIT_PRICE | float | 8 | | f | f |
superapp | ORDER_DETAIL | UNIT_COST | float | 8 | | f | f |
superapp | ORDER_DETAIL | DISCOUNT | float | 8 | | f | f |
superapp | ORDER_DETAIL | CUSTOMER_ID | int | 8 | | f | f |
(10 rows)
Code: Select all
dbadmin=> \d superapp.lu_employee
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
----------+-------------+----------------+-------------+------+---------+----------+-------------+-------------------------------
superapp | LU_EMPLOYEE | EMP_ID | int | 8 | | f | f | superapp.ORDER_DETAIL(EMP_ID)
superapp | LU_EMPLOYEE | EMP_LAST_NAME | varchar(50) | 50 | | f | f |
superapp | LU_EMPLOYEE | EMP_FIRST_NAME | varchar(50) | 50 | | f | f |
superapp | LU_EMPLOYEE | EMP_SSN | varchar(50) | 50 | | f | f |
superapp | LU_EMPLOYEE | BIRTH_DATE | date | 8 | | f | f |
superapp | LU_EMPLOYEE | HIRE_DATE | date | 8 | | f | f |
superapp | LU_EMPLOYEE | SALARY | int | 8 | | f | f |
superapp | LU_EMPLOYEE | COUNTRY_ID | int | 8 | | f | f |
superapp | LU_EMPLOYEE | DIST_CTR_ID | int | 8 | | f | f |
superapp | LU_EMPLOYEE | MANAGER_ID | int | 8 | | f | f |
superapp | LU_EMPLOYEE | LOCATION_ID | int | 8 | | f | f |
superapp | LU_EMPLOYEE | FTE_FLAG | varchar(50) | 50 | | f | f |
I am trying to create a projection like the one below and get the error ERROR 5600: Invalid predicate in projection-select. Only PK. I have the PK and FK created properly , then why am I not able to create the projection.
Code: Select all
CREATE PROJECTION superapp.order_details_proj_manual_1
(
EMP_ID,
ITEM_ID,
QTY_SOLD,
UNIT_PRICE,
DISCOUNT,
UNIT_COST
)
AS
SELECT
a11.EMP_ID,
a11.ITEM_ID,
a11.QTY_SOLD,
a11.UNIT_PRICE,
a11.DISCOUNT,
a11.UNIT_COST
FROM
SUPERAPP.ORDER_DETAIL a11 join SUPERAPP.LU_EMPLOYEE a12 on (a11.EMP_ID = a12.EMP_ID)
WHERE
a12.emp_id is not null
ORDER BY
a11.EMP_ID
SEGMENTED BY hash(ORDER_DETAIL.EMP_ID) ALL NODES;