Re: ORA-02287 error with use of a sequence number in group by clause

From: Jack Ploeg <jploeg_at_wxs.nl>
Date: 1998/05/17
Message-ID: <6jnd5g$56614_at_reader1.wxs.nl>#1/1

Matt,

the problem is that you can't use a sequence in a group by select statement. A solution (if you're using SQL*Plus) is to use the sequence just to select the starting value for your selected rows, and store this value in a variable, like this:

define START_VALUE
column start1 new_value start_value
select seq_istf_package_id.nextval start1 from dual;

This puts the nextval from your sequence in teh variable start_value. You can use this in your select statement:

select lpad(to_char(start_value + rownum ),7,'0')|| rpad(i.doc_num,30,' ')||
' '||

rpad(nvl(substr(s.store_name,1,35),' '),35,' ')||
rpad(nvl(s.addr1,' '),35,' ')||
rpad(nvl(s.addr2,' '),35,' ')||
rpad(nvl(s.city,' '),30,' ')||
rpad(s.st,5,' ')||
rpad(nvl(s.zip_cd,' '),10,' ')||

'US MSN '||

decode(sign(500-sum(round(l.qty*nvl(l.unit_cst,0),2))),-1,rpad(to_char(sum(r ound
(l.qty*nvl(l.unit_cst,0),2))),10,' '),' ')|| lpad(decode(i.srt_cd,null,decode(i.zone_cd,null,'11',substr(i.zone_cd,1,2)), substr(i.srt_cd,1,2)),2,' ')
from ist i, ist_ln l, store s
where i.dest_store_cd = s.store_cd
and i.ist_wr_dt||i.ist_store_cd||i.ist_seq_num = l.ist_wr_dt||l.ist_store_cd||
l.ist_seq_num
and (l.void_flag != 'Y' or l.void_flag is null) group by lpad(to_char(start_value + rownum),7,'0'), rpad(i.doc_num,30,' '),
' ',

rpad(nvl(substr(s.store_name,1,35),' '),35,' '),
rpad(nvl(s.addr1,' '),35,' '),
rpad(nvl(s.addr2,' '),35,' '),
rpad(nvl(s.city,' '),30,' '),
rpad(s.st,5,' '),
rpad(nvl(s.zip_cd,' '),10,' '),

'US MSN ',

lpad(decode(i.srt_cd,null,decode(i.zone_cd,null,'11',substr(i.zone_cd,1,2)), substr(i.srt_cd,1,2)),2,' ')
/

Now, update the sequence, so you'll get the right strat_value next time:

select &start_value - seq_istf_package_id.currval start1 from dual; alter sequence seq_istf_package_id increment by &start_value;

Good luck!

Jack Received on Sun May 17 1998 - 00:00:00 CEST