oracle 错误“ORA-00932：不一致的数据类型：预期 - 得到 CLOB”选择连接多个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26968728/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
error "ORA-00932: inconsistent datatypes: expected - got CLOB" select join multiple tables
提问by Nike Angreni
i got error when i use yz table that has param field with data type==> CLOB . And this is my query:
当我使用具有数据类型==> 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)
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...
有人可以帮我解决这个错误吗？谢谢...
采纳答案by Nike Angreni
finally i got the answer it just add dbms_lob.substr(yz.param,4000,1)in select and group by.
最后我得到了答案，它只是在选择和分组中添加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)
thanks for your response
FIXED
感谢您的回复 已
修复
回答by sunysen
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
