oracle - 错误 "ORA-00932: inconsistent datatypes: expected - got CLOB"选择连接多个表

Question

当我使用具有数据类型 ==> CLOB 的参数字段的 yz 表时出现错误。这是我的查询:

WITH t AS
     (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,yz.param
            ,MAX(y.seq) AS seq2
            ,MAX(y.extern_order_status) AS extern
      FROM   t_order_demand x
      JOIN   t_order_log y
      ON     x.order_id = y.order_id
      JOIN   p_catalog_status z
      ON     z.status_code_sc = y.extern_order_status
  JOIN   t_order_demand_eai yz
      ON     yz.order_id = y.order_id
      AND    y.order_id =1294
      GROUP  BY x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,y.nd1,z.status_resume,yz.param)
    SELECT *
    FROM   t
    WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)

这是错误:

ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

有人可以帮我解决这个错误吗？谢谢...

Answer 1

最后我得到了答案，只需在选择和分组依据中添加dbms_lob.substr(yz.param,4000,1)即可。

WITH t AS
             (SELECT x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,z.status_resume,y.nd1,dbms_lob.substr(yz.param,4000,1)
                    ,MAX(y.seq) AS seq2
                    ,MAX(y.extern_order_status) AS extern
              FROM   t_order_demand x
              JOIN   t_order_log y
              ON     x.order_id = y.order_id
              JOIN   p_catalog_status z
              ON     z.status_code_sc = y.extern_order_status
          JOIN   t_order_demand_eai yz
              ON     yz.order_id = x.order_id
              AND    y.order_id =1290
              GROUP  BY x.order_id,x.customer_name,y.ncli,y.ndem2,y.ndem1,y.nd2,y.nd1,z.status_resume,dbms_lob.substr(yz.param,4000,1))
            SELECT *
            FROM   t
            WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)

感谢您的回复
已修复