By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,611 Members | 2,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,611 IT Pros & Developers. It's quick & easy.

Creating Function refering to sequence

P: n/a
Hi ,
in following code I want to use the sequence object in function but it gives
the error can anyone suggest the modification to it .

CREATE FUNCTION test.test_FUN01()

RETURNS TABLE (

NAME CHARACTER,

ADD CHARACTER,

AGE INTEGER,

NATIONALITY CHARACTER,

DESIGNATION CHARACTER,

PHONE INTEGER,

MOBILE INTEGER,

EMAIL CHARACTER,

CITY CHARACTER,

STATE CHARACTER,

PINCODE INTEGER,

PAN INTEGER,

PASSPORT CHARACTER,

STATUS CHARACTER

)

RETURN SELECT

NAME,

ADD,

AGE,

NATIONALITY,

DESIGNATION,

PHONE,

MOBILE,

EMAIL,

CITY,

STATE,

PINCODE + (nextval for test.seq1) ,

PASSPORT,

STATUS

FROM test.test_TB02

;

Thanks

ARR
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
DB2 does not allow NEXT VALUE in SQL Functions.
The reason being that the semantics in that case would be different than
when it appears directly in the select list. (where it has to share
the same result with other NEXTVAL values.
Also things get really nasty once you use the function in the WHERE clause.
However, you are not the first to ask and given that Oracle's
implementation of SEQUENCE doesn't seem to give a sh** I think this
decision, in hindsight, may have been a bit patronizing on our (DB2
Dev) side.

To make a long story short, if you think this is essential open a PMR
and I'll see what I we can do for you

Note that you can use NEXT VALUE in an EXTERNAL UDF (Jaaa, C).

Cheers.
Nov 12 '05 #2

P: n/a
Serge Rielau wrote:

Note that you can use NEXT VALUE in an EXTERNAL UDF (Jaaa, C).


Serge new way of abbreviating programming languages would transform Cobol into Cool!

Jan
Nov 12 '05 #3

P: n/a
"Coool". Jan, how did you survive those IQ tests about: "What is the
next word in the series?"

Java Cobol
Jaaa ?????
Nov 12 '05 #4

P: n/a
Thanks Serge
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.