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

values(NEXTVAL FOR mySequence) instead of your SELECT statement

Cheers
Sathyaram

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.

values (nextval for CMS_APPROVALLOGID_SEQ) into var1

Hi sathyaram_s,

Thanks a lot. Problem solved....

Regards,
Deepak Ajmera

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?

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

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

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.