1 Reply Latest reply: Mar 26, 2010 12:54 PM by Zlatko Sirotic RSS

    Supertype and Subtypes

    762939
      Hello,

      I created an employees table with 3 types, although when I insert the values, it works but doesn't display the last value which is from a different type. Any idea how to fix this?


      DROP TYPE employees_type FORCE;
      DROP TYPE hourly_employees FORCE;
      DROP TYPE salaried_employee FORCE;
      DROP TYPE consultant_employee FORCE;
      DROP TABLE employees;

      CREATE OR REPLACE TYPE employees_type AS OBJECT
      (
      emp_id CHAR(4) ,
      fname VARCHAR2(20),
      lname VARCHAR2(20),
      date_hired DATE ,
      emp_type CHAR(1)
      )NOT FINAL;
      /
      CREATE OR REPLACE TYPE hourly_employees UNDER employees_type
      (
      hourly_rate NUMBER(6,2)
      )NOT FINAL;
      /
      CREATE OR REPLACE TYPE salaried_employee UNDER employees_type
      (
      annual_salary NUMBER(9,2),
      stock_option     CHAR(1)
      )NOT FINAL;
      /     
      CREATE OR REPLACE TYPE consultant_employee UNDER employees_type
      (
      contract_number NUMBER,
      billing_rate     NUMBER(8,2)
      );
      /
      CREATE TABLE employees OF employees_type;

      INSERT INTO employees
      VALUES (hourly_employees(1234,null,null,null,null,0));
        • 1. Re: Supertype and Subtypes
          Zlatko Sirotic
          SQL> SELECT TREAT(VALUE(e) AS hourly_employees)
            2    FROM employees e
            3   WHERE VALUE(e) IS OF(hourly_employees);

          TREAT(VALUE(E)ASHOURLY_EMPLOYEES)(EMP_ID, FNAME, LNAME, DATE_HIRED, EMP_TYPE, HOURLY_RATE)

          HOURLY_EMPLOYEES('1234', NULL, NULL, NULL, NULL, 0)
          or
          SQL> SELECT e.*,
            2         TREAT(VALUE(e) AS hourly_employees).hourly_rate AS hourly_rate
            3    FROM employees e
            4   WHERE VALUE(e) IS OF(hourly_employees);

          EMP_ FNAME                LNAME                DATE_HIR E HOURLY_RATE

          1234                                                                0
          Regards