Guru: Why A Function Was There But Could Not Be Found
March 25, 2019 Ted Holt
Hey, Ted:
I am stuck on trying to create a function in RPG to use in SQL. I based it on your FMTDATE function, which I successfully installed and is working great! I have been trying to get this function working for five hours and I am at my wits’ end. Hopefully, you will notice something right away.
–Andrew
The message that Andrew was receiving was SQL0204 (HISFUNCT in *LIBL type *N not found). (I have replaced the name of Andrew’s function with HISFUNC.) Yet the function existed and the service program existed. There was nothing wrong with Andrew’s RPG code or his CREATE FUNCTION statement. So why wouldn’t it run? It took us a few tries to figure it out, but the system started finding the function when he replaced CHAR parameters with VARCHAR ones.
SQL0204 is a misleading error message, but that’s due to a wonderful feature of SQL — function overloading. SQL allows you to define two or more functions with the same name as long as the parameter lists differ. The system is really telling you that no version of the function exists with a compatible parameter list, not that no version of the function exists at all. A parameter is considered compatible if the data that is passed in the call is of the type, or can be casted to the type, of the parameter definition.
Implicit casting means that the system automatically converts data from one type to another behind the scenes when it needs to and is able to. It’s not new. Skip Marchesani wrote about it in this august publication almost eight years ago. The good folks at IBM have not been sitting around working Sudoku puzzles since then, as I find more and more implicit casting that used to not work properly. IBM greatly enhanced implicit casting in IBM i 7.2, and Andrew’s system is at 7.1. I was unable to reproduce the error on a 7.2 system. Casting between CHAR and VARCHAR worked every time, no matter which way the data was defined in the database and in the function. I was even able to pass numeric literals to char(1) and varchar(1) parameters.
If you write SQL functions to carry out business processes — and I encourage you to do so — try to make the parameters as versatile (i.e., castable) as possible. I still prefer VARCHAR to CHAR because I’m confident the system will have no trouble casting CHAR arguments into VARCHAR parameters. In the same way, I often define numeric parameters as INTEGER or DOUBLE, even if I expect to use them over packed decimal and zoned decimal data, just to improve chances that the functions will always be able to handle any data that I or anyone else throws at them. Table 1 of this page from the IBM Knowledge Center has more information about the compatibility of data types.
