Generate data based on supertype/subtypes

#1

I need to generate data based on a table that is supertype/subtypes. Example of table as follows:

DROP TABLE employees_tp;
CREATE TABLE employees_tp(
emp_id_pk INTEGER NOT NULL,
badge_no DECIMAL(3,0) NOT NULL,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
employee_type CHAR(1) NOT NULL,
stock_option CHAR(1),
car_allowance CHAR(1),
profit_sharing CHAR(1),
contract_no VARCHAR(9)
);

ALTER TABLE employees_tp
ADD CONSTRAINT emp_employee_type_ck
CHECK ( employee_type IN (‘E’, ‘T’, ‘S’) );

ALTER TABLE employees_tp
ADD CONSTRAINT emp_employee_options_ck
CHECK (
(
(employee_type = ‘E’ AND stock_option = ‘Y’)
AND ( profit_sharing IS NULL )
AND ( contract_no IS NULL )
)
OR
(
(employee_type = ‘T’ AND profit_sharing = ‘Y’)
AND ( stock_option IS NULL )
AND ( car_allowance IS NULL )
AND ( contract_no IS NULL )
)
OR
(
(employee_type = ‘S’ AND contract_no = ‘Y’)
AND ( stock_option IS NULL )
AND ( car_allowance IS NULL )
AND ( profit_sharing IS NULL )
)
);

INSERT INTO employees_tp VALUES
(1, 101, ‘Joe’, ‘Bates’, ‘E’, ‘Y’, ‘Y’, NULL, NULL),
(2, 101, ‘Sally’, ‘Jones’, ‘E’, ‘Y’, NULL, NULL, NULL),
(3, 103, ‘Pete’, ‘Smith’, ‘T’, NULL, NULL, ‘Y’, NULL);

#2

The best way to do this is to use formulas (the f(x) button at the end of each row) to conditionally return nil when the desired conditions are not met.

For example, the formula for the profit sharing field would be something like:

if (employee_type == 'E' and stock_option == 'Y') or (employee_type == 'S' and contract_no == 'Y') then nil else this end.

1 Like