This discussion is archived
10 Replies Latest reply: Jun 12, 2006 7:23 AM by William Robertson RSS

USE sequence.nextval in SQL statement

516501 Newbie
Currently Being Moderated
Hi,

I want to use SEQUENCE_name.NEXVAL in our query.
But it gives error
ora-02287: sequence number not allowed here
-----------------------
Select distinct dd.agreementid,dd.cityid,dd.bankid,dd.bankbranchid,zz.bankbranchdesc,
account_no,account_type,Sysdate,'A',Null,'1001',Sysdate,NULL,NULL,
ELEC_PMNT_SEQ.Nextval seq_num
From pdi.pdi_instr_d_tmp dd,
pdi.pdi_bankbranch_m zz,
pdi.pdi_bankaccount_tmp t
Where t.agreementid = dd.agreementid
And dd.cityid = zz.cityid
And dd.bankid = zz.bankid
And dd.bankbranchid= zz.bankbranchid
And dd.mc_status = 'M' And dd.status = 'M' And instr_type <> 'P'

Thanks & Regards
K S Ratan
  • 1. Re: USE sequence.nextval in SQL statement
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    Hi,

    You cannot use sequence.nextval in select statement with distinct clause.
    If you want the number of output row, use rownum, or other like this, instead.

    Nicolas.

    Message was edited by:
    N. Gasparotto
  • 2. Re: USE sequence.nextval in SQL statement
    516501 Newbie
    Currently Being Moderated
    Hi,

    Is there any other way? Actually we have to use SEQUENCE. Can we use sequence by using execute immediate?

    Ratan
  • 3. Re: USE sequence.nextval in SQL statement
    32685 Expert
    Currently Being Moderated
    Hello

    Just to clarify, you can't use it because there is a sort implied by the distinct:
    SQL> create sequence dt_test_seq;

    Sequence created.

    SQL> select dt_test_seq.nextval from dual;

      NEXTVAL
    ---------
            1

    SQL> select distinct dt_test_seq.nextval from dual;
    select distinct dt_test_seq.nextval from dual
                                *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here


    SQL> select dt_test_seq.nextval from dual order by 1;
    select dt_test_seq.nextval from dual order by 1
                       *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here
    Not exactly sure why using a sort prevents the use of a sequence, but I'll try to find out :-)

    David

    Message was edited by:
    david_tyler


    Also, I really don't think you want a distinct in there, as each row will be unique because of the value returned by the sequence!
  • 4. Re: USE sequence.nextval in SQL statement
    27876 Newbie
    Currently Being Moderated
    Actually we have to use SEQUENCE.
    Please expplain the business need that you are trying to solve using this and we can see what best alternative you have.
  • 5. Re: USE sequence.nextval in SQL statement
    117914 Newbie
    Currently Being Moderated
    nextval cannot be used with distinct or group functions:

    select dbg_id field1, syn_seq_geral.nextval field2 from syn_debug group by 1, 2
    --ERROR

    select distinct dbg_id field1, syn_seq_geral.nextval field2 from syn_debug
    --ERROR

    select dbg_id field1, syn_seq_geral.nextval field2 from syn_debug
    --OK

    Try to create a temporary table, insert all data in it and then use the DISTINCT in this table.

    Regards,
    J Hime
  • 6. Re: USE sequence.nextval in SQL statement
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    SCOTT@LSC01> create sequence s;
    
    Sequence created.
    
    SCOTT@LSC01> select distinct job,s.nextval from emp;
    select distinct job,s.nextval from emp
                          *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here
    
    SCOTT@LSC01> select job, s.nextval from (select distinct job from emp);
    JOB          NEXTVAL
    --------- ----------
    CLERK              1
    SALESMAN           2
    PRESIDENT          3
    MANAGER            4
    ANALYST            5
    Message was edited by:
    Laurent Schneider

    I am using 10gR2, note that DISTINCT does not sort the rows ;-)
  • 7. Re: USE sequence.nextval in SQL statement
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    You can use a subquery, but maybe have you need an order by clause ?
    SQL> ed
    Wrote file afiedt.buf

      1* select distinct username, MySeq.nextval from dba_users
    SQL> /
    select distinct username, MySeq.nextval from dba_users
                                    *
    ERROR at line 1:
    ORA-02287: sequence number not allowed here


    SQL> ed
    Wrote file afiedt.buf

      1  select a.*, MySeq.nextval
      2* from (select distinct username from dba_users order by username) a
    SQL> /

    USERNAME                          NEXTVAL
    ------------------------------ ----------
    DBSNMP                                  1
    H89UCBAC                                2
    OUTLN                                   3
    PEOPLE                                  4
    PS                                      5
    SYS                                     6
    SYSTEM                                  7

    7 rows selected.

    SQL> /

    USERNAME                          NEXTVAL
    ------------------------------ ----------
    DBSNMP                                  8
    H89UCBAC                                9
    OUTLN                                  10
    PEOPLE                                 11
    PS                                     12
    SYS                                    13
    SYSTEM                                 14

    7 rows selected.
    Is this do you want ? Sequence will increment on each query execution, or did you want the number of output line to have the same result on each sql execution :
    SQL> ed
    Wrote file afiedt.buf

      1  select a.*, rownum
      2* from (select distinct username from dba_users order by username) a
    SQL> /

    USERNAME                           ROWNUM
    ------------------------------ ----------
    DBSNMP                                  1
    H89UCBAC                                2
    OUTLN                                   3
    PEOPLE                                  4
    PS                                      5
    SYS                                     6
    SYSTEM                                  7

    7 rows selected.

    SQL> /

    USERNAME                           ROWNUM
    ------------------------------ ----------
    DBSNMP                                  1
    H89UCBAC                                2
    OUTLN                                   3
    PEOPLE                                  4
    PS                                      5
    SYS                                     6
    SYSTEM                                  7

    7 rows selected.

    SQL>
    Nicolas.

    Sorry Laurent, you've already showed that works with subquery...
    Message was edited by:
    N. Gasparotto
  • 8. Re: USE sequence.nextval in SQL statement
    516501 Newbie
    Currently Being Moderated
    Yes.

    I got the solution.

    Thanks to you all for the support.

    Ratan
  • 9. Re: USE sequence.nextval in SQL statement
    Laurent Schneider Oracle ACE
    Currently Being Moderated
    it is ok nicolas, next time it will happened to me :-)
  • 10. Re: USE sequence.nextval in SQL statement
    William Robertson Oracle ACE
    Currently Being Moderated
    (Oops, must remember to press 'Refresh' before replying...)

    Message was edited by:
    William Robertson