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?

Welcome to StackOverflow: if you post code, XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! If you post error messages, **PLEASE** use the blockquotes ( ` " ` ) to properly format the error message. — marc_s, Nov 15 '12 at 07:50

score 3 · Answer 1 · answered Nov 15 '12 at 10:15

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

Unpivot tables script error

1 Answers1