Oracle SQL排除CLOB字段含'rich text'记录报错求助

错误原因分析 #

你遇到的ORA-00932: inconsistent datatypes: expected - got CLOB错误，是因为原查询中NOT IN子查询返回的是CLOB类型的NRS.FULL_TEXT，但你用它和PT.PATIENT_MRN（非CLOB类型，通常是VARCHAR2或数字）做比较，两种数据类型无法直接匹配，导致报错。

另外直接用NOT LIKE处理CLOB字段无结果，大概率是因为Oracle对CLOB的LIKE支持有局限性（比如大CLOB内容、字符集兼容问题），更可靠的方式是用CLOB专用函数。

修正后的查询代码 #

SELECT 
    PT.PATIENT_MRN AS MRN
    ,ORD.PATIENT_ID
    ,ORD.ORDER_DATE
    ,ORD.ORDER_TYPE
    ,ORD.ORDER_PROC
    ,ORD.SPECIMEN_SOURCE
    ,ORS.COMPONENT_NAME
    ,ORS.RESULT_TEXT
    ,NRS.FULL_TEXT
FROM RDM.PATIENT                PT
LEFT JOIN RDM.ORDERS            ORD ON PT.PATIENT_ID = ORD.PATIENT_ID 
LEFT JOIN RDM.ORDER_RESULT      ORS ON ORS.ORDER_ID = ORD.ORDER_ID
LEFT JOIN RDM.NOTE_RSLT         NRS ON ORD.ORDER_ID = NRS.ORDER_ID
WHERE ORD.ORDER_DATE BETWEEN '01-JUL-2021 12:00:00 AM' AND '30-JUN-2022 11:59:59 PM'
AND FLOOR(MONTHS_BETWEEN(SYSDATE, PT.BIRTH_DATE)/12) > 18   
AND NOT EXISTS (
    SELECT 1 
    FROM RDM.NOTE_RSLT sub_nrs
    WHERE sub_nrs.ORDER_ID = ORD.ORDER_ID
    AND DBMS_LOB.INSTR(sub_nrs.FULL_TEXT, 'rich text') > 0
)

关键调整说明 #

• 用NOT EXISTS替代NOT IN：避免跨数据类型的比较问题，同时NOT EXISTS的逻辑更直接——只要当前订单对应的NOTE_RSLT记录中没有含'rich text'的，就保留这条数据。
• 使用DBMS_LOB.INSTR判断CLOB内容：这是Oracle专门为CLOB类型设计的字符串查找函数，相比LIKE更稳定，能处理大体积的CLOB数据，返回指定字符串在CLOB中的位置，返回值>0即表示包含目标文本。

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