10 Replies Latest reply: Jun 12, 2006 9:23 AM by William Robertson RSS

    USE sequence.nextval in SQL statement

    516501
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        Yes.

                        I got the solution.

                        Thanks to you all for the support.

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

                            Message was edited by:
                            William Robertson