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

Thanks for the help. Actually, I ended up creating a view first for everything I needed (the aggregate rows) except the sequence number, then I selected the rows from the view with the sequence number incremented for each row, and then I dropped the view.

-- 
Matt Brennan
SQL*Tools Specialist
GERS Retail Systems
9725-C Scranton Road
San Diego, California 92121
1-800-854-2263
mbrennan_at_gers.com
(Original email address is spam-blocked.)

Jack Ploeg <jploeg_at_wxs.nl> wrote in article
<6jnd5g$56614_at_reader1.wxs.nl>...


> 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