CODEWITHSUNDEEP
oracle-databaseselectjoinclob
Nike Angreni
Nike Angreni

Reputation: 71

error "ORA-00932: inconsistent datatypes: expected - got CLOB" select join multiple tables

i got error when i use yz table that has param field with data type==> CLOB . And this is my query:

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"

anybody can help me to fix this error? thanks...

Upvotes: 3

Views: 24549

Answers (2)

Nike Angreni
Nike Angreni

Reputation: 71

finally i got the answer it just add dbms_lob.substr(yz.param,4000,1) in 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 response
FIXED

Upvotes: 2

sunysen
sunysen

Reputation: 2351

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

Upvotes: 0

Related Questions