oracle - 逆透视表脚本错误

Question

我有一个名为 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'
   );

我正在对此运行一个逆透视查询。

 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);

但是我得到一个错误

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

我这里做错了什么？

Answer 1

问题是您正在尝试 UNPIVOT 数据类型不同的列。所有逆透视的值都必须相同，类似于使用 UNION 或 UNION ALL 查询时，列中的每个值都必须具有相同的数据类型。

因为 RM_ID 列是一个 number 而其他列是 varchar2 那么你需要你需要使用一个子查询来执行RM_ID 列的 cast():

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);

参见 SQL Fiddle with Demo语法工作