MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Userdefined function with variable number of parameters

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Thu Nov 05, 2020 12:31 pm    Post subject: Userdefined function with variable number of parameters Reply with quote
The requirement is something like this:

Code:


SELECT X FROM MYTABLE
WHERE MYTABLE.FIELD1 = UDF(1,2,3,...);


CREATE FUNCTON UDF
    ( P1, P2,P3 ....)
    RETURNS INTEGER
    -- DO SOMETHING
    RETURN ...




One field in a where clause should be prepared by a user defined function, pure native SQL, no REXX, C etc. function.

The number of parameters for the function is variable.

The DB2 zOS syntax says, this it not possible; my understanding.

Creating a type CREATE TYPE myType BIGINT ARRAY [] is possible, but not accepted for a user defined function as parameter.

How is it possible to pass a variable number of parameters to a userdefined function?

Or: Stored procedures seems to accept array parameters. But how to call the procedure inside a native SQL query? How to setup the array just inside a SQL query?

Any hints are welcome.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12366
Topics: 75
Location: San Jose
PostPosted: Thu Nov 05, 2020 12:52 pm    Post subject: Reply with quote
bauer,

Can't you define the max possible values to UDF and then pass the values with default values?

For example if the UDF accepts a max of 10 parms then

Code:

SELECT X FROM MYTABLE
WHERE MYTABLE.FIELD1 = UDF(1,2,3,,,,,,,,);

** comma separates each parm - so make sure to have "n" commas


And the UDF can be

Code:

CREATE FUNCTON UDF
    ( P1,P2,P3,P4,P5,P6,P7,P8,P9,P10)
    RETURNS INTEGER
    -- DO SOMETHING
    RETURN ...

_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
PostPosted: Thu Nov 05, 2020 1:13 pm    Post subject: Reply with quote
Yes, max number of parameters works.
Not so nice, but might be a solution.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group