Unpivot tables script error

Question

I have a table called rp_resolution_master.

CREATE TABLE "REQUEST_PORTAL"."RP_RESOLUTION_MASTER" 
   ( "RM_ID" NUMBER, 
    "SR_ID" NUMBER, 
    "REQUEST_STATUS" VARCHAR2(200 BYTE), 
    "COMMENTS" VARCHAR2(4000 BYTE), 
    "UPDATED_ON" DATE, 
    "UPDATED_BY" VARCHAR2(500 BYTE), 
    "INTERNAL_COMMUNICATION" VARCHAR2(1 BYTE) DEFAULT 'N'
   );

I am running an unpivot query on this.

 SELECT *
   From Rp_Resolution_Master
Unpivot INCLUDE NULLS ( 
          value For measures In ( rm_id, request_status, Comments
                                , updated_by, internal_communication)
                      ) 
 where sr_id = 1004707 
   And Updated_On = ( Select Max(Updated_On) 
                        From Rp_Resolution_Master 
                       Where Sr_Id = 1004707);

But I get an error

ORA-01790: expression must have same datatype as corresponding expression 01790. 00000 - "expression must have same datatype as corresponding expression" *Cause:
*Action: Error at Line: 3 Column: 51

What is it that I am doing wrong here?

Answer 1

The problem is that you are attempting to UNPIVOT columns that are not the same datatype. All of the values being unpivoted must be the same, similar to when you use a UNION or UNION ALL query, each value in the columns must be of the same datatype.

Since the RM_ID column is a number and the others are varchar2 then you need to you will need to use a subquery which to perform a cast() of the RM_ID column:

select *
from 
(
  SELECT cast(rm_id as varchar2(200)) rm_id,
    request_status,
    Comments,
    updated_by,
    internal_communication,
    Updated_On,
    sr_id
  From Rp_Resolution_Master
)
Unpivot INCLUDE NULLS ( 
          value For measures In ( rm_id, request_status, Comments
                                , updated_by, internal_communication)
                      ) 
where sr_id = 1004707 
  And Updated_On = ( Select Max(Updated_On) 
                     From Rp_Resolution_Master 
                     Where Sr_Id = 1004707);

See SQL Fiddle with Demo of the syntax working