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

From: Matt Brennan <mbrennan_at_gers.antispam.com>
Date: 1998/05/12
Message-ID: <01bd7dde$c73cbc60$049a0580_at_mcb>#1/1

I'm spooling data to a file and using a sequence number generator to number each record and I get an ORA-02287 error. When I look up the error, it tells me this:

---

Cause: The specified number reference, CURRVAL or NEXTVAL, is inappropriate at this point in the statement.

Action: Check the syntax, then relocate ot remove the sequence number.

---

That doesn't tell me much on *how* to correct it. <Grrrr!>

Here's my SQL:

select lpad(to_char(seq_istf_package_id.nextval),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)), subs
tr(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(seq_istf_package_id.nextval),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)), subs
tr(i.srt_cd,1,2)),2,' ')
/

Here's the error I get:

group by lpad(to_char(seq_istf_package_id.nextval),7,'0'),

                                          *

ERROR at line 17:
ORA-02287: sequence number not allowed here

Just for kicks, I've also run it without contcatenating the sequence with the rest of the select (but rather as its own column), but I get the same error. I'm using concatenation because I'm spooling to a fixed-length file and there are no spaces between the fields (I have no control over that, unfortunately).

I don't think I can use rownum because I need to have a sequence number that doesn't restart at '1' - I need to pick up from where I left off (plus one) in a previous run on the script each time the script is run.

I've also checked "Oracle - The Complete Reference", but it doesn't say anything about restrictions on using sequences in a group by clause.

Help! Any ideas? Thanks in advance...

Oh, and if you reply to my email by hitting "reply to author", please take out the "antispam." part, as it's spam-blocked.

-- 
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.)

Received on Tue May 12 1998 - 00:00:00 CEST