原查询语句如下
select c.id, c.s_id, c.s_name, c.r_id, c.r_name, c.c_cont, c.s_time, c.c_status, c.motion, c.filename
from usps.Chatbox c inner join usps.chat_note_info n on n.c_id=c.id
where n.r_id=2 and n.c_state='1'
group by c.id, c.s_id, c.s_name, c.r_id, c.r_name, c.c_cont, c.s_time, c.c_status, c.motion, c.filename
order by c.s_time ASC c_cont为blob型字段。
如果放在group by中,则会报出ORA-00932:不一致的数据类型:要求-得到的却是BLOB
如不放入group by中,则该查询也会出错说c.c_cont不是group by表达式
c_cont字段的内容必须要查询出来的。
高手们。请问这种情况怎么处理好?请帮帮忙,不胜感激!!!
select c.id, c.s_id, c.s_name, c.r_id, c.r_name, c.c_cont, c.s_time, c.c_status, c.motion, c.filename
from usps.Chatbox c inner join usps.chat_note_info n on n.c_id=c.id
where n.r_id=2 and n.c_state='1'
group by c.id, c.s_id, c.s_name, c.r_id, c.r_name, c.c_cont, c.s_time, c.c_status, c.motion, c.filename
order by c.s_time ASC c_cont为blob型字段。
如果放在group by中,则会报出ORA-00932:不一致的数据类型:要求-得到的却是BLOB
如不放入group by中,则该查询也会出错说c.c_cont不是group by表达式
c_cont字段的内容必须要查询出来的。
高手们。请问这种情况怎么处理好?请帮帮忙,不胜感激!!!
--使用to_char转换一下
SELECT c.id,
c.s_id,
c.s_name,
c.r_id,
c.r_name,
to_char(c.c_cont),
c.s_time,
c.c_status,
c.motion,
c.filename
FROM usps.Chatbox c
INNER JOIN usps.chat_note_info n
ON n.c_id = c.id
WHERE n.r_id = 2 AND
n.c_state = '1'
GROUP BY c.id,
c.s_id,
c.s_name,
c.r_id,
c.r_name,
to_char(c.c_cont),
c.s_time,
c.c_status,
c.motion,
c.filename
ORDER BY c.s_time ASC;
-- lob 类型列不能用于 group by 子句中
-- 从语句上分析,如果 usps.Chatbox 表中没有重复记录,应该可以改写为
select id, s_id, s_name, r_id, r_name, c_cont, s_time, c_status, motion, filename
from usps.Chatbox
where id in (select c_id from usps.chat_note_info where r_id=2 and c_state='1')
order by s_time asc;
这样转换依然会报出ORA-00932:不一致的数据类型:要求NUMBER-得到的却是BLOB