SELECT
XBGATHER_ACCEPTER_PAY.OPER_ID,
GATHER_NO,
USERNAME,
bill_list,
SUM(PRICE_SUM) AS SUM
FROM XBGATHER_ACCEPTER_PAY
LEFT JOIN PT_USER ON USERNO = OPER_ID
WHERE OP_TIME >= TO_DATE('2010-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND OP_TIME <= TO_DATE('2010-09-27 23:59:59','yyyy-mm-dd hh24:mi:ss')
AND IS_DEL='0'
AND OPER_ID='999'
GROUP BY XBGATHER_ACCEPTER_PAY.OPER_ID;上面语句报不是group by表达式错误,我将非聚合函数字段都加入group by;
GROUP BY XBGATHER_ACCEPTER_PAY.OPER_ID, GATHER_NO, USERNAME, bill_list;
这时报:不一致的数据类型: 要求 - 得到的却是 CLOB;原因bill_list是clob类型;
把bill_list从select与group by中删除,select成功;但我现在需要同时把bill_list也select出来;
应该怎么解决这个问题,请各位朋友帮帮忙。
XBGATHER_ACCEPTER_PAY.OPER_ID,
GATHER_NO,
USERNAME,
bill_list,
SUM(PRICE_SUM) AS SUM
FROM XBGATHER_ACCEPTER_PAY
LEFT JOIN PT_USER ON USERNO = OPER_ID
WHERE OP_TIME >= TO_DATE('2010-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND OP_TIME <= TO_DATE('2010-09-27 23:59:59','yyyy-mm-dd hh24:mi:ss')
AND IS_DEL='0'
AND OPER_ID='999'
GROUP BY XBGATHER_ACCEPTER_PAY.OPER_ID;上面语句报不是group by表达式错误,我将非聚合函数字段都加入group by;
GROUP BY XBGATHER_ACCEPTER_PAY.OPER_ID, GATHER_NO, USERNAME, bill_list;
这时报:不一致的数据类型: 要求 - 得到的却是 CLOB;原因bill_list是clob类型;
把bill_list从select与group by中删除,select成功;但我现在需要同时把bill_list也select出来;
应该怎么解决这个问题,请各位朋友帮帮忙。
解决方案 »
- Oracle触发器使用replace函数之后,insert报插入null错误
- 高分求解-oracle 对executeBatch的支持?
- 求助xml文件数据到入oracle 11g
- asp 操作 oracle数据库的问题
- 如何用ORACLE客户端修改服务器的系统时间?
- 触发器无效且未通过重新确认
- 急需一 select 写法 急!!!!!!!!!!!!!
- 帮我看看这个包抱错
- 游标的使用问题
- 请问那里可以下载oracle9i文档盘里的那个有sql语法图的文档啊?
- Oracle存储过程,在一个for循环里面,要catch到异常,然后continue或者跳出循环怎么做
- 关于ORACLE语法问题 请教了
XBGATHER_ACCEPTER_PAY.OPER_ID,
GATHER_NO,
USERNAME,
bill_list,
SUM(PRICE_SUM) AS SUM
FROM XBGATHER_ACCEPTER_PAY
LEFT JOIN PT_USER ON USERNO = OPER_ID
WHERE OP_TIME >= TO_DATE('2010-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND OP_TIME <= TO_DATE('2010-09-27 23:59:59','yyyy-mm-dd hh24:mi:ss')
AND IS_DEL='0'
AND OPER_ID='999'
GROUP BY XBGATHER_ACCEPTER_PAY.OPER_ID, GATHER_NO, USERNAME, bill_list;
ID NAME VALUE
----------- -------------------------------------------------------------------------------- -----------
1 123 1
1 123 2
2 1234 2
SQL> select id,name,sum(value) from test_clob_group group by id,name;
select id,name,sum(value) from test_clob_group group by id,name
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL> select id,dbms_lob.substr(name),sum(value) from test_clob_group group by id,dbms_lob.substr(name);
ID DBMS_LOB.SUBSTR(NAME) SUM(VALUE)
----------- -------------------------------------------------------------------------------- ----------
1 123 3
2 1234 2
SQL>
p.OPER_ID,
GATHER_NO,
USERNAME,
(select bill_list from XBGATHER_ACCEPTER_PAY where OPER_ID=p.OPER_ID and rownum=1) bill_list,
SUM(PRICE_SUM) AS SUM
FROM XBGATHER_ACCEPTER_PAY p
LEFT JOIN PT_USER u ON USERNO = OPER_ID
WHERE OP_TIME >= TO_DATE('2010-09-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND OP_TIME <= TO_DATE('2010-09-27 23:59:59','yyyy-mm-dd hh24:mi:ss')
AND IS_DEL='0'
AND OPER_ID='999'
GROUP BY p.OPER_ID, GATHER_NO, USERNAME;