Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
Overloaded SQL Function Limitations (UDF)
has no ratings.
Published: 01 Mar 2013
Revised: 01 Mar 2013 - 1729 days ago
Last viewed on: 24 Nov 2017 (4453 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Overloaded SQL Function Limitations (UDF) Published by: Bob Cozzi on 01 Mar 2013 view comments(2)

When I wrote the cvtDate() SQL function in SQL, the documentation gave me the impression that I could define so called overloaded functions. Basically these different functions that have the same name, but different parameter types or parameter count.

So I initially created two of them:

cvtDate( inDate dec(8,0),  fmt varchar(4))

and

cvtDate( inDate dec(8,0) )

Not too different at all. Apparently you can't have optional parameter so the practice is to define multiple functions, that is, use Overloading.

After the usual development cycle, I decided I wanted to also support converting dates stored in character fields, to true dates. After all, it would be cool to process the date values in an outfile from DSPOBJD or others by wrapping them in cvtDate().

So I defined yet another function as follows:

cvtDate( inDate varchar(128) , fmt varchar(16) )

and

cvtDate( inDate varchar(128) )

This would mean I could specify one function, CVTDATE() and not worry if the date in the file was stored in numeric or character (so long as I knew the format in which it was stored).

Sadly, it won't let me compile these second set of function "Function CVTDATE already exists."

I am not certain if it is something I'm doing or if this is a known limitation or a bug, either way, it it basically means that I have to define yet another function, perhaps named charDate() that will do what I need it to.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Overloaded SQL Function Limitations (UDF)
Posted: 4 years 8 months 25 days 2 hours 41 minutes ago

Located the solution thanks again to R. Bruce Hoffman.

You have to include the SPECIFIC keyword on the CREATE FUNCTION statement. Use it to assign unique name to each function overload. Basically you are specifying the signature yourself and forcing it to differentiate func( varchar(8) ) from func( dec(8,0) ).

See the linked function to view this keyword in use.

Posted by: Ringer
Premium member *
Comment on: Overloaded SQL Function Limitations (UDF)
Posted: 4 years 8 months 25 days 59 minutes ago

That's correct. And that is how the sysfuncs and sysprocs system catalogs link to sysparms, by the specific name, not the routine name. Also, if you have to drop such an overloaded function/stored proc, you would do so using the specific name as well.

Chris Ringer