高手请看****ora-01467*****

tszhao168

oracle 高手请您帮助：
    我在一个pb+oracle8I 的环境 写 如下sql:
select distinct a.data_id as Account_no,
a.seq_no as Seq_no ,
c.template_id as template_id ,
max(a.action_date) as action_date ,
max(d.name) as template_name,  
max(a.user_id) as Make_by ,
max(lpad('-', 1)) as check_by,
b.row_id as row_id ,
max(Decode(b.field_name,'TESTLENGTH1',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH1 ,
max(Decode(b.field_name,'TESTLENGTH2',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH2 ,
max(Decode(b.field_name,'TESTLENGTH3',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH3 ,
max(Decode(b.field_name,'TESTLENGTH4',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH4 ,
max(Decode(b.field_name,'TESTLENGTH5',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH5 ,
max(Decode(b.field_name,'TESTLENGTH6',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH6 ,
max(Decode(b.field_name,'TESTLENGTH7',nvl(old_data,'-'), NULL)) as Before_TESTLENGTH7 ,
max(Decode(b.field_name,'CHANGE',nvl(old_data,'-'), NULL)) as Before_CHANGE ,
max(Decode(b.field_name,'DFGFDG',nvl(old_data,'-'), NULL)) as Before_DFGFDG ,
max(Decode(b.field_name,'TESTLEGTH10',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH10 ,
max(Decode(b.field_name,'TESTLEGTH11',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH11 ,
max(Decode(b.field_name,'TESTLEGTH12',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH12 ,
max(Decode(b.field_name,'TESTLEGTH13',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH13 ,
max(Decode(b.field_name,'TESTLEGTH14',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH14 ,
max(Decode(b.field_name,'TESTLEGTH15',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH15 ,
max(Decode(b.field_name,'TESTLEGTH16',nvl(old_data,'-'), NULL)) as Before_TESTLEGTH16 ,
max(Decode(b.field_name,'TESTLENGTH1' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH1 ,
max(Decode(b.field_name,'TESTLENGTH2' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH2 ,
max(Decode(b.field_name,'TESTLENGTH3' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH3 ,
max(Decode(b.field_name,'TESTLENGTH4' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH4 ,
max(Decode(b.field_name,'TESTLENGTH5' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH5 ,
max(Decode(b.field_name,'TESTLENGTH6' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH6 ,
max(Decode(b.field_name,'TESTLENGTH7' ,nvl(cur_data,'-'), NULL)) as After_TESTLENGTH7 ,
max(Decode(b.field_name,'CHANGE' ,nvl(cur_data,'-'), NULL)) as After_CHANGE ,
max(Decode(b.field_name,'DFGFDG' ,nvl(cur_data,'-'), NULL)) as After_DFGFDG ,
max(Decode(b.field_name,'TESTLEGTH10' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH10 ,
max(Decode(b.field_name,'TESTLEGTH11' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH11 ,
max(Decode(b.field_name,'TESTLEGTH12' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH12 ,
max(Decode(b.field_name,'TESTLEGTH13' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH13 ,
max(Decode(b.field_name,'TESTLEGTH14' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH14 ,
/* max(Decode(b.field_name,'TESTLEGTH15' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH15 , */
max(Decode(b.field_name,'TESTLEGTH16' ,nvl(cur_data,'-'), NULL)) as After_TESTLEGTH16   
from crm_log_master a,sdm_log_detail b,TEMPLATE_FIELD c,template d  
where c.template_id = d.template_id and c.field_name = b.field_name and  
      c.source_name =d.source_name and c.source_name = a.source_name and d.repeat_grp = 'Y'
      and a.seq_no = b.seq_no and a.source_name = b.source_name and a.action_type in ('W','X')
      and b.source_name = 'account' and a.loc = 'HKG' and a.data_id like '123%' and d.template_id = 't21'
      and a.action_date between to_date('2003-09-09 00:00:01','YYYY-MM-DD HH24:Mi:SS')
      and to_date('2003-09-09 23:59:59' ,'YYYY-MM-DD HH24:Mi:SS') and a.user_id like '%'
      group by a.data_id,c.template_id, a.seq_no, b.row_id
order by account_no,template_name,seq_no,row_id
在程序执行时，出现错误代码ora-01467
怎样解决？期待您的回复！！！

maybelc

ORA-01467 sort key too long

Cause: A DISTINCT, GROUP BY, ORDER BY, or SET operation requires a sort key longer than that supported by Oracle. Either too many columns or too many group functions were specified in the SELECT statement.

Action: Reduce the number of columns or group functions involved in the operation.

不知道有没有帮助,顺便帮我解决一个用pro*c编写pl/sql的错误.外边可以找到,不胜感激

maybelc

不好意思,不在这个里边下边是错误帮个忙,我快要郁闷死了

Error at line 38, column 26 in file lctest.pc
select 'lc' from dual;
.........................1
PLS-S-00201, identifier 'DUAL' must be declared
Error at line 38, column 4 in file lctest.pc
select 'lc' from dual;
...1
PLS-S-00000, SQL Statement ignored
Semantic error at line 36, column 3, file lctest.pc:
BEGIN
..1
PCC-S-02346, PL/SQL found semantic errors
make: The error code from the last command is 1.


谢谢

rollingpig

distinct似乎没有必要
去掉看看？