Re: ORA-02287 error with use of a sequence number in group by clause
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