A User-Defined Function (UDF) is a routine that can be called from within an SQL statement, very similar to the way that a subprocedure can be called from an RPG expression. You can write UDFs both in SQL itself and in external languages such as RPG. External UDFs in RPG are quickly becoming a core skill for folks who are trying to write modern code. They are crucial for using the database in a modern way and for enabling RPG routines to be reused in web applications.

Since they have become so popular, I've noticed a lot of people asking how to use optional parameters in a UDF. Optional parameters in a UDF work a little differently than *nopass parameters in RPG, as they are implemented using a mechanism known as "overloading."

In fact, many RPG programmers that I know think overloading is a feature that should be added to RPG. Although it's unavailable in RPG today, you can still utilize it through SQL statements in RPG, and you can write overloaded UDFs by having SQL call RPG routines. SQL UDFs provide a great way to give overloaded functions a try.

The Overloading Concept

The basic concept of overloading is pretty straightforward. The idea is that you have more than one routine with the exact same name. Imagine two separate subprocedures with identical names in the same service program. The program would call the appropriate one based upon the parameters passed to it.

The best way to illustrate this is to start with something you already know. Bear with me for a moment and consider the following RPG subprocedure:

      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      *  getMonthName():  Retrieve the month name
      *
      *       MonthNo = month number (1-12) to get month from
      *
      *  Returns the month name or ' upon error
      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P getMonthName    B                   export
     D getMonthName    PI            32A   varying
     D   MonthNo                     10I 0 value

      /free
         select;
         when MonthNo = 1;
            return 'January';
         when MonthNo = 2;
            return 'February';
         when MonthNo = 3;
            return 'March';
         when MonthNo = 4;
            return 'April';
         when MonthNo = 5;
            return 'May';
         when MonthNo = 6;
            return 'June';
         when MonthNo = 7;
            return 'July';
         when MonthNo = 8;
            return 'August';
         when MonthNo = 9;
            return 'September';
         when MonthNo = 10;
            return 'October';
         when MonthNo = 11;
            return 'November';
         when MonthNo = 12;
            return 'December';
         other;
            return ';
         endsl;
      /end-free
     P                 E

This is an example of a procedure you might use in many places. You pass it a number from 1-12, and it returns the name of the corresponding month. If you use subprocedures, then you shouldn't see anything new or exciting in the preceding code.

Now what if you wanted to overload this routine? Instead of receiving a number as a parameter (as in the preceding code) you wanted to receive a date field. Again, RPG doesn't support overloading, but if it did you'd be able to have a second subprocedure with the exact same name that calls the first subprocedure.

     P getMonthName2   B                   export
     D getMonthName2   PI            32A   varying
     D   DateFld                       D   const
      /free
          monitor;
            return getMonthName(%subdt(DateFld: *MONTHS));
          on-error *all;
            return '';
          endmon;
      /end-free
     P                 E

All this procedure does is extract the month name from the date field (using the %SUBDT BIF) and call the original subprocedure with the month number.

Of course, you can't do overloading in RPG subprocedures today, so I "cheated" and named the preceding routine getMonthName2, instead of getMonthName. So you'll have to use your imagination a bit, but imagine what it would be like if the preceding routine were named getMonthName and therefore had the same name as the earlier one.

If true overloading were available in RPG, the two routines would have the same name, and RPG would determine which one to call based on the parameters. If you passed a number, it would call the first procedure (the one that accepts a number as a parameter). If you passed a date field, it would call the second procedure. That's what overloading is.

Overloading As an Optional Parameter Mechanism

Since I stated that overloading is how you handle optional parameters in SQL, let me explain how overloading relates to optional parameters.

Languages like Java and SQL do not have the concept of optional parameters. In RPG, if you code a parameter with options(*nopass), you don't have to pass that parameter. It's optional. That's impossible in Java or SQL. In those other languages, you'd have to write two separate (and overloaded!) routines. One with the optional parameter(s), and one without.

For example, let's say you wanted to get the price for an item number. In your shop, there are separate prices for each item that you sell, and separate prices for each business channel that you sell products in.

Now let's say that the business channel parameter is optional. There's a default business channel (we'll call it 'X') that you should use in your price calculations if the channel parameter isn't provided.

With that in mind, your routine to retrieve a price might be coded this way:

Price = getPrice( ItemNo: Channel );
-or-
Price = getPrice( ItemNo );

In RPG, you'd typically do that by making Channel an optional parameter. You'd check the %PARMS count to see if Channel is passed, and if it's not, you'd default it to X in the code. But imagine what it would be like if RPG (like Java and SQL) didn't support optional parameters and instead used overloading. In that case, you'd code it like this:

     P getPrice        B                   export
     D getPrice        PI             9p 2
     D   itemno                      10a   const
     D   channel                      1a   const
      /free
         // do whatever logic is needed to get price
         return Price;
      /end-free
     P                 E

      // this won't currently work in RPG, because RPG
      // doesn't support overloading:
     P getPrice        B                   export
     D getPrice        PI             9p 2
     D   itemno                      10a   const
      /free
         return getPrice(itemno: 'X');
      /end-free
     P                 E

Again, if overloading was supported, it would look up the correct routine by the parameters passed to it. In this case, if two parameters are passed, it would call the first getPrice(), and if only one parameter were passed, it would call the second one. The second one, in turn, calls the first one and fills in the missing parameter. That's how Java, SQL, and many other languages handle optional parameters using overloading.

Overloading in SQL

OK, enough background. Enough hypothetical stuff. It's time to write some real code, stuff that you can actually try to run. SQL already contains the mechanism for overloading. When you call an SQL UDF, it doesn't locate the routine based purely on the function name. Instead, it looks for a function that accepts the same parameters that you requested.

Indeed, that's a common "gotcha" for people new to UDFs. They write their function, run it, and the system says SQL0204 - myFunction in *LIBL type *N not found. After a lot of frustrating debugging, they finally discover that the function is in their *LIBL exactly as they expected it to be, but they passed the parameter in VARCHAR format when the routine expected CHAR. Or they passed a CHAR when the routine expected a DECIMAL. This seems frustrating! Why does the system say the routine is not found when in fact the problem was that the wrong parameters were passed?

The reason, of course, is because SQL supports overloading. It doesn't look up the function based solely on the function's name! It looks up the function based on the functions name and its parameters.

Consider the following two Create Function SQL statements:

Create Function toYMD(numdate decimal(6, 0))
       returns decimal(8, 0)
       language RPGLE
       deterministic
       no sql
       external name 'my-lib/DATEUTIL(MDY2YMD)'
       parameter style general;

Create Function toYMD(chardate varchar(10))
       returns decimal(8, 0)
       language RPGLE
       deterministic
       no sql
       external name 'my-lib/DATEUTIL(MDY2YMD2)'
       parameter style general;

If you ran both of these Create Function statements, they'd both succeed. Even though there's already a toYMD UDF in the same library, the second statement would still succeed. That's because SQL supports overloading. The UDFs are not looked up solely by name. They're also looked up by parameters, and since these two functions accept different parameters (one is a number, the other a varchar), they're considered separate functions, despite having the same name.

Thanks to SQL's "external name" option (on the preceding Create Function statements), the name of the SQL function itself doesn't have to match the name of the RPG routine that it calls. So despite the fact that these UDFs have the same name on the SQL side, under the covers they call separate RPG routines--one is designed to convert a numeric field from Month/Day/Year format to Year/Month/Day format, and the other is designed to do the same thing with a character string.

Here's the RPG code that these functions call:

     H NOMAIN OPTION(*SRCSTMT)

      *  To Compile:
      *>  CRTRPGMOD DATEUTIL DBGVIEW(*LIST)
      *>  CRTSRVPGM DATEUTIL EXPORT(*ALL)

     D MDY2YMD         PR             8p 0
     D   numdate                      6p 0 const
     D MDY2YMD2        PR             8p 0
     D   chardate                    10a   varying const

     P MDY2YMD         B                   export
     D MDY2YMD         PI             8p 0
     D   numdate                      6p 0 const
     D ret             s              8p 0
      /free
        monitor;
          ret = %dec( %date(numdate:*mdy) : *iso );
        on-error;
          ret = -1;
        endmon;
        return ret;
      /end-free
     P                 E


     P MDY2YMD2        B                   export
     D MDY2YMD2        PI             8p 0
     D   chardate                    10a   varying const
     D ret             s              8p 0
      /free
        monitor;
          select;
          when %len(chardate)=6;
            ret = %dec( %date(chardate:*mdy0) : *iso );
          when %len(chardate)=8 and %subst(chardate:3:1)='/';
            ret = %dec( %date(chardate:*mdy/) : *iso );
          when %len(chardate)=8 and %subst(chardate:3:1)='-';
            ret = %dec( %date(chardate:*mdy-) : *iso );
          when %len(chardate)=8;
            ret = %dec( %date(chardate:*usa0) : *iso );
          when %len(chardate)=10 and %subst(chardate:3:1)='/';
            ret = %dec( %date(chardate:*usa/) : *iso );
          other;
            ret = %dec( %date(%xlate('-':'/':chardate):*usa/) : *iso );
          endsl;
        on-error;
          ret = -1;
        endmon;
        return ret;
      /end-free
     P                 E

The first routine expects the input date to always be six digits. The second routine will handle a character string that's 6, 8, or 10 characters long. It'll look for either dash or slash as a date separator. So it'll handle almost any date, as long as it's a character string Month/Day/Year format. (Of course, these routines don't handle other input formats, like DMY, but you could easily write those routines if you need them.)

Once I've run these Create Function statements and compiled the RPG code, I have an overloaded UDF. It can be called with either a numeric or character date. For example:

    select toYMD(PoDate), PoNum from PURCHORD;     // PoDate is a 10A field in MM/DD/YYYY format
-or-
    select toYMD(OrdDat), OrdNo, Cust from ORDER;  // OrdDat is a 6P 0 field in MMDDYY format
-or-
    select toYMD(digits(FifoDate)) from FININV;    // FifoDate is a 8S 0 field in MMDDYYYY format,
                                                      but digits() converts it to varchar.

So the toYMD() routine is overloaded. It accepts a date in either character or numeric format. I could, of course, overload it further to accept dates in date format. I could also add additional parameters, all using this same mechanism.

Optional Parameter SQL Example

I regularly see questions about optional parameters to UDFs in the forums. In my opinion, the easiest and most robust way to handle optional parameters is to use overloading as described in this article.

So here's an example of overloading to handle optional parameters in a UDF:

Create Function getPrice(itemno varchar(10))
       returns decimal(9, 2)
       language RPGLE
       deterministic
       no sql
       external name 'my-lib/PRICING(GETPRICE1)'
       parameter style general;

Create Function getPrice(itemno varchar(10), channel varchar(1))
       returns decimal(9, 2)
       language RPGLE
       deterministic
       no sql
       external name 'my-lib/PRICING(GETPRICE2)'
       parameter style general;

As before, the idea is that you have a getPrice() that can have either one or two parameters. If the second parameter (Channel) is not passed, it should be defaulted to X.

I've implemented overloading in this example, and I've told it to call a getPrice1() RPG subprocedure when there's one parameter and getPrice2() subprocedure when there are two parameters.

Here's the corresponding RPG code. (The prices returned are just a mock-up, since your method of calculating prices is likely to be different from mine. You'll need to insert the proper logic for your own shop!)

     H NOMAIN OPTION(*SRCSTMT)

      *  To compile:
      *>  CRTRPGMOD PRICING DBGVIEW(*LIST)
      *>  CRTSRVPGM PRICING EXPORT(*ALL)

     D getPrice1       PR             9p 2
     D   itemno                      10a   const varying
     D getPrice2       PR             9p 2
     D   itemno                      10a   const varying
     D   channel                      1a   const varying


     P getPrice1       B                   export
     D getPrice1       PI             9p 2
     D   itemno                      10a   const varying
      /free
         return getPrice2(itemno: 'X');
      /end-free
     P                 E


     P getPrice2       B                   export
     D getPrice2       PI             9p 2
     D   itemno                      10a   const varying
     D   channel                      1a   const varying
     D Price           s              9p 2
      /free
         // do whatever logic is needed to get price
         if channel='X';
            Price = 560.50;
         elseif channel='Y';
            Price = 2000.00;
         else;
            Price = 54321.67;
         endif;
         return Price;
      /end-free
     P                 E

The Wrong and Dangerous Way to Handle Optional Parameters

Unfortunately, I've seen a lot of folks handle optional parameters to a UDF by having both overloaded routines call the same underlying RPG routine, and by using options(*omit). In my opinion that technique is dangerous--and unsupported.

For example, you might have two Create Function statements for the getPrice UDF as I provided in the last section, except they might both point to the same RPG subprocedure, also named getPrice. Then, you might use this commonly posted (but incorrect) technique to check the parameters:

     P getPrice        B                   export
     D getPrice        PI             9p 2
     D   itemno                      10a   const varying
     D                                     options(*nopass:*omit)
     D   in_channel                   1a   const varying
     D                                     options(*nopass:*omit)
     D Price           s              9p 2
     D Channel         s              1a   inz('X') 
      /free
         // Don't do this:
         if %parms>=2 or %addr(in_channel)<>*null;
             Channel = in_channel;
         endif;

         // do whatever logic is needed to get price
         if channel='X';
            Price = 560.50;
         elseif channel='Y';
            Price = 2000.00;
         else;
            Price = 54321.67;
         endif;
         return Price;
      /end-free
     P                 E

This is frequently posted as a solution in the public forums, and it's wrong and in my opinion dangerous. Why is that?

First problem: The %PARMS BIF gets its information from an operational descriptor. SQL doesn't provide that descriptor, so the value of %PARMS is unpredictable.

• %PARMS is never set when SQL calls an RPG subprocedure. Why check it if you know it's never set?
• Although %PARMS will usually return -1 when called from SQL perhaps 99 percent of the time, this behavior is not guaranteed. Under the covers, RPG is checking uninitialized memory. If it happens to be a valid pointer (by some bad fortune!), %PARMS might not return -1.
• Even if called from RPG (or another language in which %PARMS will be set properly), it's not valid to use %PARMS OR %ADDR. You must have %PARMS and %ADDR, since the value of %ADDR isn't guaranteed unless the parm is passed. Again, it'll work 99 percent of the time this way because the odds of randomly selected memory in the system containing a valid pointer are slim. But if it does happen to be a valid pointer, this will cause your parameter to be set to garbage.

These types of errors are amongst the most frustrating and elusive errors, since the routine works 99 percent of the time. You test it, and everything works great. But it'll fail sporadically in production, and nobody will know why!

The bottom line is that SQL doesn't understand the concept of checking %PARMS or %ADDR for optional parameters, so it doesn't set up the parameter list using these RPG features. Don't do it that way; use overloading instead!

Checking Optional Parameters by Using the Specific Name

Another way of handling optional parameters that is valid is to use the SQL specific name. I will not cover that technique in this article, but I will explain that technique in a future article.

Conclusion

Overloading is a simple and powerful way to handle differing parameter lists, whether it be due to different data types or due to optional parameters. Although RPG itself doesn't support overloading, you can still write overloaded SQL statements that use RPG as its external routine.

Give it a try!