Well the copy paste on the code didn’t work so well and I don’t see an edit option…

H NOMAIN
*
D ITRVEHLOC       PR            50A
D   Unit                         6A
D   ProxType                     5A
 *
P ITRVEHLOC       B                   EXPORT
 *
D ITRVEHLOC       PI            50A
D Unit                           6A
D ProxType                       5A
 *
D Location        S             50A
 *
C                   Call      'OMGETLCR'            
C                   Parm                    Unit    
C                   Parm                    ProxType
C                   Parm                    Location
 *                                                  
C                   Return                  Location
 *
P ITRVEHLOC       E

CRTSRVPGM SRVPGM(OMNISITE/ITRVEHLOC)
MODULE(OMNISITE/ITRVEHLOC)
EXPORT(*ALL)
TEXT('Service Program for mod ITRVEHLOC')

Create Function
  ITRVEHLOC(
          UNIT        Char(  6),
          PROXTO      Char(  5))
  Returns CHAR
  Language            RPGLE
  Specific            ITRVEHLOC
  Deterministic
  No SQL
  Returns Null on Null Input
  No External Action
  Not Fenced
  External Name       'OMNISITE/ITRVEHLOC(ITRVEHLOC)'
  Parameter Style     SQL

So I found this in another forum and it actual makes the Select Statement work. But I really don’t like “cast”ing parameters in the Select to fix this. Other suggestions?

“This is the classic issue with functions & stored procedures – you
defined the parameters as character but are passing literals. Literals
are cast to varying char type by SQL, so the prototype does not match.

You could define all the parameters as varying, too.”

It’ll be a while before I can create a test that uses your code, but I can make suggestions.

First:

D   ProxType                     5A

I might change that to:

D   ProxType                     5A   varying const

Along with that:

         PROXTO      Char(  5))

…becomes:

         PROXTO      Varchar(  5))

I’m pretty sure that SQL isn’t going to generate a CHAR parm from a literal string like ‘CITY ‘. I would expect it to create a VARCHAR parameter instead. I don’t know about UNUNIT, but that would reasonably be a CHAR.

With those above changes, the function call in your SELECT statement prepares a CHAR and a VARCHAR parameter, then DB2 goes searching for a function named ITRVEHLOC() that has one CHAR and one VARCHAR parm. It manages to find such a definition and executes the CALL to your program.

Without those changes, it does manage to find ITRVEHLOC(), but the parms don’t match. (Note that functions and stored procs may overload parm definitions.) It looks for another ITRVEHLOC() with matching parms, but none exist.

The quoted text you supplied notes that “You could define all the parameters as varying, too.” That’s essentially what it was getting at.

Tom

BTW, you might also succeed by CASTing ‘CITY ‘ as CHAR in your function call. I’d prefer having the work done in my definitions rather than forcing every potential SELECT to have to code for it.

If I can get time, I’ll try to generate an actual test of your code. But I’m going to be busy for a few days.

Tom

Great suggestions Tom. I think you’ve pointed me in the right direction. Thanks.

I puzzled why the error message was “not found”. When you pointed out the overloading bit it all made sense. I hope IBM can improve this error handling and logging to provide better insight into the matter.

When you pointed out the overloading bit it all made sense.

When you “know” that a function or proc exists but it is reported as ‘Not found’, you should be able to do some testing by CASTing each parameter to match explicitly with the definition. If explicit CASTing then finds the target, you can be sure of the type of problem.

That’s about as easy of a test as can be done.

Tom