奇怪的Group by问题

Question

select 
  to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24'),
  count(*)
from report_call_hist 
where report_call_hist.calltype='IN'
group by to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24')
order by to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24')为何这种写法不对?报ORA-01722 无效数字,请高手解决,本人刚从MS SQL转过来的,真不适应Oracle这种语法,比MS SQL差太远了~~

Answer 1

语法没问题，检查其它SQL> create table report_call_hist(calltype varchar2(30),datetime date);Table createdSQL> insert into report_call_hist select 'IN',SYSDATE FROM DUAL;1 row insertedSQL> insert into report_call_hist select 'OUT',SYSDATE FROM DUAL;1 row insertedSQL> 
SQL> select
  2    to_char(report_call_hist.datetime,'YYYY'),
  3    to_char(report_call_hist.datetime,'MM'),
  4    to_char(report_call_hist.datetime,'DD'),
  5    to_char(report_call_hist.datetime,'HH24'),
  6    count(*)
  7  from report_call_hist
  8  where report_call_hist.calltype='IN'
  9  group by to_char(report_call_hist.datetime,'YYYY'),
 10    to_char(report_call_hist.datetime,'MM'),
 11    to_char(report_call_hist.datetime,'DD'),
 12    to_char(report_call_hist.datetime,'HH24')
 13  order by to_char(report_call_hist.datetime,'YYYY'),
 14    to_char(report_call_hist.datetime,'MM'),
 15    to_char(report_call_hist.datetime,'DD'),
 16    to_char(report_call_hist.datetime,'HH24');TO_CHAR(REPORT_CALL_HIST.DATET TO_CHAR(REPORT_CALL_HIST.DATET TO_CHAR(REPORT_CALL_HIST.DATET TO_CHAR(REPORT_CALL_HIST.DATET   COUNT(*)
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------
2005                           06                             05                             14                                      1SQL>

Answer 2

还是不行,郁闷
SQL> 
SQL> select
  2    to_char(report_call_hist.datetime,'YYYY'),
  3    to_char(report_call_hist.datetime,'MM'),
  4    to_char(report_call_hist.datetime,'DD'),
  5    to_char(report_call_hist.datetime,'HH24'),
  6    count(*)
  7  from report_call_hist
  8  where report_call_hist.calltype='IN'
  9  group by to_char(report_call_hist.datetime,'YYYY'),
 10    to_char(report_call_hist.datetime,'MM'),
 11    to_char(report_call_hist.datetime,'DD'),
 12    to_char(report_call_hist.datetime,'HH24')
 13  order by to_char(report_call_hist.datetime,'YYYY'),
 14    to_char(report_call_hist.datetime,'MM'),
 15    to_char(report_call_hist.datetime,'DD'),
 16    to_char(report_call_hist.datetime,'HH24')
 17  ;select
  to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24'),
  count(*)
from report_call_hist
where report_call_hist.calltype='IN'
group by to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24')
order by to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24')ORA-01722: 无效数字SQL>

Answer 3

ORA-01722: 无效数字
估计是你用to_char()转化的时候出问题的,report_call_hist.datetime数据有问题,有字符
select report_call_hist.datetime from report_call_hist
where report_call_hist.calltype='IN'你先看下是不是的

Answer 4

里面的数据应该没什么问题
SELECT report_call_hist.datetime FROM report_call_hist
  2  WHERE report_call_hist.calltype='IN'
  3  ;DATETIME
------------------------------
2007-5-10 2:21:39
2007-5-10 2:22:02
2007-5-10 2:22:08
2007-5-10 2:22:19
2007-5-10 2:22:20
2007-5-10 2:22:21
2007-5-10 2:22:21
2007-5-10 2:22:24
2007-5-10 2:22:26
2007-5-10 2:22:34
2007-5-10 2:22:38
2007-5-10 2:23:09
2007-5-10 2:23:23
2007-5-10 2:23:40
2007-5-10 2:23:53
2007-5-10 2:24:20
2007-5-10 2:24:26
2007-5-10 2:24:54
2007-5-10 2:24:57
2007-5-10 2:17:57DATETIME
------------------------------

Answer 5

怪就怪在老报无效的数字,Oracle真TMD 的神经病
SELECT 
  TO_CHAR(datetime,'YYYY'),
  TO_CHAR(datetime,'MM'),
  TO_CHAR(datetime,'DD'),
  TO_CHAR(datetime,'HH24'),
  COUNT(*)
FROM REPORT_CALL_HIST 
WHERE calltype='IN'
GROUP BY TO_CHAR(datetime,'YYYY'),
  TO_CHAR(datetime,'MM'),
  TO_CHAR(datetime,'DD'),
  TO_CHAR(datetime,'HH24')
ORDER BY TO_CHAR(datetime,'YYYY'),
  TO_CHAR(datetime,'MM'),
  TO_CHAR(datetime,'DD'),
  TO_CHAR(datetime,'HH24');

Answer 6

刚才试了下
好像是group by里面不能写to_char()
你先这样试下
select
  to_char(report_call_hist.datetime,'YYYY'),
  to_char(report_call_hist.datetime,'MM'),
  to_char(report_call_hist.datetime,'DD'),
  to_char(report_call_hist.datetime,'HH24'),
  count(*)
from report_call_hist
where report_call_hist.calltype='IN'
group by report_call_hist
要是不行的话这样再试下
select
  to_char(report_call_hist.datetime,'YYYY'),
  count(*)
from report_call_hist
where report_call_hist.calltype='IN'

Answer 7

select
  to_char(report_call_hist.datetime,'YYYY'),
  count(*)
from report_call_hist
where report_call_hist.calltype='IN'ORA-00937 此句报非单组分组函数,但是好像是group by里面不能写to_char()
,不太可能吧,Oracle也算是一个大的数据库了,不会连这种基本的功能都不支持,郁闷.
我去掉to_char倒是可以执行:
select
  2  datetime,
  3  --to_char(datetime,'YYYY'),
  4  --dnis,
  5  count(*)
  6  from report_call_hist
  7  where calltype='IN'
  8  group by --to_char(datetime,'YYYY')
  9  datetime
 10  ;DATETIME                         COUNT(*)
------------------------------ ----------
2007-5-10 0:00:22                       1
2007-5-10 0:07:30                       1
2007-5-10 0:07:37                       1
2007-5-10 0:12:19                       1
2007-5-10 0:12:56                       1
2007-5-10 0:13:24                       1
2007-5-10 0:17:11                       1
2007-5-10 0:18:15                       1
2007-5-10 0:19:05                       1
2007-5-10 0:19:09                       1
2007-5-10 0:21:57                       2
2007-5-10 0:21:59                       1
2007-5-10 0:23:27                       1
2007-5-10 0:25:42                       1
2007-5-10 0:25:44                       1
2007-5-10 0:26:10                       1
2007-5-10 0:26:20                       1
2007-5-10 0:26:29                       1
2007-5-10 0:26:36                       1
2007-5-10 0:26:49                       1DATETIME                         COUNT(*)
真是服了Oracle,乱七八糟的东西一大砣,唉,真是没办法,难搞的客户一定要用这个垃圾数据库,害死我了........真不想做了....在MS SQL上这点项目一点问题都没,现在用Oracle,举步维坚,存储过程更怪,唉.....

Answer 8

用group by你前面的select应该用sum(..)。

Answer 9

语法应该没问题,前面hyrongg不是也测试过了
请检查下别的吧
PS:别不会用就说oracle差....

Answer 10

加入ORACLE的群吧；群号是：19312711

Answer 11

数据有问题MS SQL 和 oracle 不是一个等级的产品，别瞎扯到一起

Answer 12

可能是我基础不好吧,唉,被BOSS批评了~~~~