rob@XE_11g2> CREATE VIEW v_emp AS

SELECT E.*,

CASE WHEN employee_id IN

  (SELECT UNIQUE manager_id FROM employees E) THEN ‘mgr’ ELSE ‘stf’ END emptype

FROM employees E;

View created.

rob@XE:11g2> desc v_emp;

Name             Null?      Type

===============  =========  ============

EMPLOYEE_ID                 NUMBER(6)

FIRST_NAME                  VARCHAR2(20)

LAST_NAME        NOT NULL   VARCHAR2(25)

EMAIL            NOT NULL   VARCHAR2(25)

PHONE_NUMBER                VARCHAR2(20)

HIRE_DATE        NOT NULL   DATE

JOB_ID           NOT NULL   VARCHAR2(10)

SALARY                      NUMBER(8,2)

COMMISSION_PCT              NUMBER(2,2)

MANAGER_ID                  NUMBER(6)

DEPARTMENT_ID               NUMBER(4)

EMPTYPE                     CHAR(3)

rob@XE_11g2> SELECT * FROM

   (

    SELECT

       D.department_name AS Organization,

       E.emptype,

       E.employee_id,

       E.salary

    FROM v_emp E, departments D

    WHERE E.department_id IS NOT NULL

    AND E.manager_id IS NOT NULL

    AND D.department_id = E.department_id

    ORDER BY 1

   )

   PIVOT

   (

    COUNT(employee_id) AS headcount,

    SUM(salary) AS totpay,

    AVG(salary) AS avgpay

    FOR (emptype) IN (‘stf’, ‘mgr’)

   );

   FOR (emptype) IN

        *

ERROR at line 22:

ORA-56904: pivot value must have datatype that is convertible to pivot column