467,881 Members | 1,220 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,881 developers. It's quick & easy.

Creating Function refering to sequence

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
  • viewed: 1892
Share:
4 Replies
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
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
"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
Thanks Serge
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by JKop | last post: by
3 posts views Thread by Matt Smolic | last post: by
13 posts views Thread by Nagib Abi Fadel | last post: by
2 posts views Thread by pshvarts | last post: by
21 posts views Thread by dragoncoder | last post: by
2 posts views Thread by ais523 | last post: by
1 post views Thread by mmm | last post: by
reply views Thread by MrMoon | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.