Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Posts
    20

    Question Unanswered: Getting nextval from sequence in a variable

    Hi All,

    How can I get the nextVal from a sequence into a variable. I have tried using :

    set (var) = (select (nextval for mySequence) from sysibm.sysdummy1);

    but I get an error saying
    "NEXTVAL FOR mySequence" cannot be specified in this context.

    I also tried
    SELECT (NEXTVAL FOR mySequence) into v_var from sysibm.sysdummy1;
    but got an error saying
    An unexpected token "var" was found following "mySequence) into". Expected tokens may include: "<space>"

    Where am I going wrong? Is there any other method?

    Thanks
    Deepak

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Getting nextval from sequence in a variable

    values(NEXTVAL FOR mySequence) instead of your SELECT statement

    Cheers
    Sathyaram

    Originally posted by deepaka
    Hi All,

    How can I get the nextVal from a sequence into a variable. I have tried using :

    set (var) = (select (nextval for mySequence) from sysibm.sysdummy1);

    but I get an error saying
    "NEXTVAL FOR mySequence" cannot be specified in this context.

    I also tried
    SELECT (NEXTVAL FOR mySequence) into v_var from sysibm.sysdummy1;
    but got an error saying
    An unexpected token "var" was found following "mySequence) into". Expected tokens may include: "<space>"

    Where am I going wrong? Is there any other method?

    Thanks
    Deepak
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2004
    Posts
    20

    Re: Nextval for sequence. db2v7

    Thanks Sathyaram,

    But still I am unable to compile the function. When I use

    set (tmp) = values (nextval for CMS_APPROVALLOGID_SEQ);

    I get an error message saying
    administrator.FUNCTION1: 7: [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "VALUES" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=7. SQLSTATE=42884

    I tried changing this to
    set (tmp) = values nextval for CMS_APPROVALLOGID_SEQ;
    but got
    administrator.FUNCTION1: 7: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "nextval" was found following "; set (tmp) = values". Expected tokens may include: "CONCAT". LINE NUMBER=7. SQLSTATE=42601


    Using
    set (tmp) = values nextval for CMS_APPROVALLOGID_SEQ from sysibm.sysdummy1;
    gives
    administrator.FUNCTION1: 7: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "nextval" was found following "; set (tmp) = values". Expected tokens may include: "(". LINE NUMBER=7. SQLSTATE=42601


    Using
    set (tmp) = values (nextval for CMS_APPROVALLOGID_SEQ) from sysibm.sysdummy1;
    gives
    administrator.FUNCTION1: 7: [IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token "from" was found following "S_APPROVALLOGID_SEQ)". Expected tokens may include: "->". LINE NUMBER=7. SQLSTATE=42601

    What is going wrong? tmp is declared as int and all this processing is going on in a function and not a procedure though I have not tried this in a procedure.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Nextval for sequence. db2v7

    values (nextval for CMS_APPROVALLOGID_SEQ) into var1
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jan 2004
    Posts
    20
    Hi sathyaram_s,

    Thanks a lot. Problem solved....

    Regards,
    Deepak Ajmera

  6. #6
    Join Date
    Jan 2004
    Posts
    20

    Question

    Hi,

    Guess I was a little too fast in posting my previous comment. It is working in stored procedures but not in triggers or functions. I need the same functionality in triggers too.
    Another question is:
    Is it possible to call stored procedures from triggers?

  7. #7
    Join Date
    Aug 2003
    Posts
    21
    Deepak,
    (i) There is a set of manuals provided by IBM exclusively for DB2. Go
    through those documents.
    (ii) Search this board before posting a question. Your question on calling stored procedures from triggers has been answered umpteen times.


    As an additional note, read the 'Read Me' threads at the top. There has been enormous amount of effort put to compile and post them for
    (y)our benefit ...

    Thanks

  8. #8
    Join Date
    Jan 2004
    Posts
    20
    Guys,

    Finally I was able to get it right. The statement is:

    set var = nextval for sequenceName;

    Simple....


    Thank you all for yr replies...

    Regards,
    Deepak Ajmera

  9. #9
    Join Date
    Jan 2004
    Posts
    7

    Re: Getting nextval from sequence in a variable

    Originally posted by deepaka
    Hi All,

    How can I get the nextVal from a sequence into a variable. I have tried using :

    set (var) = (select (nextval for mySequence) from sysibm.sysdummy1);

    but I get an error saying
    "NEXTVAL FOR mySequence" cannot be specified in this context.

    I also tried
    SELECT (NEXTVAL FOR mySequence) into v_var from sysibm.sysdummy1;
    but got an error saying
    An unexpected token "var" was found following "mySequence) into". Expected tokens may include: "<space>"

    Where am I going wrong? Is there any other method?

    Thanks
    Deepak

    Hi Deepak,

    If you are using DB2 then the way you can get the next val from a sequence is

    VALUES NEXTVAL FOR mySequence

    This will return a one row one column output which will be the next value in the sequence.

    You do not need to get it from sysibm.sysdummy1. The above query will return the expected output.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •