Clever Geek Handbook

Error "ORA-00932: Inconsistent data types: expected - CLOB received" select a join of multiple tables

I got an error when I am using yz table which has a param field with data type ==> CLOB. And this is my request:

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)

and this is the error:

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

Can anyone help me fix this error? thank...

+3


source to share


2 answers


Finally I got an answer that just adds dbms_lob.substr (yz.param, 4000,1) to select and group by.

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)


thanks for your answer
Fixed

+2


source 


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)
    SELECT *
    FROM   t
    WHERE  (t.seq2 || t.extern) IN (SELECT MAX(tt.seq2 || tt.extern) FROM t tt)




lob fields can not be used as a group by


0


source






More articles:


All Articles