Oracle中提取日期年份转数字的方法及类型不匹配报错排查

我来帮你排查这个SQL报错的问题！你遇到的inconsistent datatypes: expected NUMBER got DATE报错，核心原因其实很容易忽略——你在SELECT语句里确实把日期字段转成了数字，但WHERE子句里根本没用到这些转换后的结果，还是直接拿原DATE类型的字段和数字类型的c26_year_built做比较，这就导致了类型不匹配。

先看你的原SQL逻辑：

• 在SELECT部分，你对pu.C38_UPDT_HEAT等四个日期字段做了TO_NUMBER(EXTRACT(year FROM ...))转换，还起了别名，但这些别名只能在SELECT或ORDER BY中使用，WHERE子句是无法识别的。
• 而WHERE子句里的条件pu.c26_year_built > pu.C38_UPDT_HEAT，这里pu.C38_UPDT_HEAT是DATE类型，c26_year_built是数字类型，Oracle会尝试把数字转成DATE去匹配，这显然不符合你的预期，所以触发了报错。

解决办法1：在WHERE子句中直接对日期字段做转换 #

把每个比较的日期字段都用同样的TO_NUMBER(EXTRACT(year FROM ...))处理，确保两边都是数字类型，就能正常比较了。修改后的完整SQL如下：

select 
    pc.a00_pnum,
    pc.a06_edition,
    pu.c26_year_built,
    TO_NUMBER(EXTRACT(year FROM pu.C38_UPDT_HEAT)) "yr_ht_updtd",
    TO_NUMBER(EXTRACT(year FROM pu.C35_UPDT_PLUMB)) "yr_plumb_updtd",
    TO_NUMBER(EXTRACT(year FROM pu.C34_UPDT_WIRE)) "yr_wire_updtd",
    TO_NUMBER(EXTRACT(year FROM pu.C22_UPDT_ROOF)) "yr_roof_updtd"
from tfprpt.pcommon pc 
join tfprpt.punit pu on pc.a00_pnum = pu.a00_pnum and pc.a06_edition = pu.a06_edition 
where 
    pc.a06_edition = (select max(pc2.a06_edition) from tfprpt.pcommon pc2 where pc2.a00_pnum = pc.a00_pnum) 
    and pc.a09_xdate >= '11-May-18' 
    and pc.d14_status = 'I' 
    and ( 
        pu.c26_year_built > TO_NUMBER(EXTRACT(year FROM pu.C38_UPDT_HEAT)) 
        or pu.c26_year_built > TO_NUMBER(EXTRACT(year FROM pu.C35_UPDT_PLUMB)) 
        or pu.c26_year_built > TO_NUMBER(EXTRACT(year FROM pu.C34_UPDT_WIRE)) 
        or pu.c26_year_built > TO_NUMBER(EXTRACT(year FROM pu.C22_UPDT_ROOF)) 
    );

解决办法2：用CTE先处理转换，再比较（更易维护） #

如果不想重复写转换逻辑，可以先把需要的转换字段在CTE（公共表表达式）里处理好，再在外层做比较，代码更简洁也更便于后续维护：

WITH unit_updates AS (
    SELECT 
        pu.a00_pnum,
        pu.a06_edition,
        pu.c26_year_built,
        TO_NUMBER(EXTRACT(year FROM pu.C38_UPDT_HEAT)) AS yr_ht_updtd,
        TO_NUMBER(EXTRACT(year FROM pu.C35_UPDT_PLUMB)) AS yr_plumb_updtd,
        TO_NUMBER(EXTRACT(year FROM pu.C34_UPDT_WIRE)) AS yr_wire_updtd,
        TO_NUMBER(EXTRACT(year FROM pu.C22_UPDT_ROOF)) AS yr_roof_updtd
    FROM tfprpt.punit pu
)
SELECT 
    pc.a00_pnum,
    pc.a06_edition,
    uu.c26_year_built,
    uu.yr_ht_updtd,
    uu.yr_plumb_updtd,
    uu.yr_wire_updtd,
    uu.yr_roof_updtd
FROM tfprpt.pcommon pc
JOIN unit_updates uu ON pc.a00_pnum = uu.a00_pnum AND pc.a06_edition = uu.a06_edition
WHERE 
    pc.a06_edition = (select max(pc2.a06_edition) from tfprpt.pcommon pc2 where pc2.a00_pnum = pc.a00_pnum) 
    and pc.a09_xdate >= '11-May-18' 
    and pc.d14_status = 'I' 
    and ( 
        uu.c26_year_built > uu.yr_ht_updtd 
        or uu.c26_year_built > uu.yr_plumb_updtd 
        or uu.c26_year_built > uu.yr_wire_updtd 
        or uu.c26_year_built > uu.yr_roof_updtd 
    );

两种方法都能解决类型不匹配的问题，第二种更适合后续需要修改转换逻辑的场景，避免重复代码。

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