Oracle窄表转宽视图时如何按指定列设置对应数据类型？

错误信息 #

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

问题说明 #

我有一张窄格式的表，需要创建宽格式视图。由于VALUE列混合存储了字符串、整数和浮点数，特意添加ORACLE_DTYPES列来指定视图对应字段的数据类型，但使用CASE语句创建视图时触发了上述数据类型不一致的错误，请问如何修改视图语句，让视图返回正确的数据类型？

示例表 #

建表语句 #

CREATE TABLE mytable (
    "REVISION" NUMBER NOT NULL ENABLE,
    "COLOR" VARCHAR(50) NOT NULL ENABLE,
    "PARAMETER" VARCHAR(50) NOT NULL ENABLE,
    "VALUE" VARCHAR(50) NOT NULL ENABLE,
    "ORACLE_DTYPES" VARCHAR(50) NOT NULL ENABLE
)

原视图创建语句（报错版本） #

CREATE OR REPLACE VIEW myview AS
SELECT
    "REVISION",
    "COLOR",
    MAX(CASE "PARAMETER" WHEN 'param1' THEN
            CASE "ORACLE_DTYPES"
                WHEN 'FLOAT' THEN TO_NUMBER("VALUE")
                WHEN 'INTEGER' THEN TO_NUMBER("VALUE")
                ELSE TO_CHAR("VALUE")
            END
        ELSE NULL END),
    MAX(CASE "PARAMETER" WHEN 'param2' THEN
            CASE "ORACLE_DTYPES"
                WHEN 'FLOAT' THEN TO_NUMBER("VALUE")
                WHEN 'INTEGER' THEN TO_NUMBER("VALUE")
                ELSE TO_CHAR("VALUE")
            END
        ELSE NULL END),
    MAX(CASE "PARAMETER" WHEN 'param3' THEN
            CASE "ORACLE_DTYPES"
                WHEN 'FLOAT' THEN TO_NUMBER("VALUE")
                WHEN 'INTEGER' THEN TO_NUMBER("VALUE")
                ELSE TO_CHAR("VALUE")
            END
        ELSE NULL END),
    MAX(CASE "PARAMETER" WHEN 'param4' THEN
            CASE "ORACLE_DTYPES"
                WHEN 'FLOAT' THEN TO_NUMBER("VALUE")
                WHEN 'INTEGER' THEN TO_NUMBER("VALUE")
                ELSE TO_CHAR("VALUE")
            END
    ELSE NULL END)
FROM
    mytable
GROUP BY
    "REVISION", "COLOR"
ORDER BY
    "REVISION", "COLOR"

期望的视图结果及数据类型 #

视图结果 #

数据类型 #

REVISION NUMBER
COLOR VARCHAR
param1 NUMBER
param2 NUMBER
param3 VARCHAR
param4 VARCHAR

解决方案 #

错误原因 #

Oracle的CASE语句要求所有分支返回相同数据类型，原语句中同一个CASE嵌套里同时返回TO_NUMBER（数值类型）和TO_CHAR（字符类型），导致Oracle无法确定该列的最终数据类型，触发类型不一致错误。

修改后的视图语句 #

观察示例表可知，每个PARAMETER对应的ORACLE_DTYPES是固定的（比如param1永远是整数，param2永远是浮点数），因此可以直接针对每个参数固定转换对应的数据类型，无需嵌套判断ORACLE_DTYPES：

CREATE OR REPLACE VIEW myview AS
SELECT
    "REVISION",
    "COLOR",
    MAX(CASE "PARAMETER" WHEN 'param1' THEN TO_NUMBER("VALUE") END) AS param1,
    MAX(CASE "PARAMETER" WHEN 'param2' THEN TO_NUMBER("VALUE") END) AS param2,
    MAX(CASE "PARAMETER" WHEN 'param3' THEN "VALUE" END) AS param3,
    MAX(CASE "PARAMETER" WHEN 'param4' THEN "VALUE" END) AS param4
FROM
    mytable
GROUP BY
    "REVISION", "COLOR"
ORDER BY
    "REVISION", "COLOR"

说明 #

• param1和param2统一转换为NUMBER类型，符合期望的数据类型要求；
• param3和param4直接保留原VALUE的字符类型；
• 每个CASE分支仅返回单一类型，避免了数据类型冲突，同时满足宽表的格式需求。

内容的提问来源于stack exchange，提问作者Simon1