• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    RELATED STORIES

    Casting Between Data Types

    Promotion Of Data Types

    SQL Implicit Cast of Character Strings and Numeric Values

    Formatting Dates With SQL, Take 3

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: FHG, Four Hundred Guru, IBM i, RPG, SQL

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    A HATS For Many Occasions Five Skills You Don’t Need To Manage IBM i (And Two You Do)

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 19

This Issue Sponsored By

  • T.L. Ashford
  • COMMON
  • CNX
  • WorksRight Software
  • Computer Keyes

Table of Contents

  • Rebuilding The Bottom Of The Pyramid
  • Five Skills You Don’t Need To Manage IBM i (And Two You Do)
  • Guru: Why A Function Was There But Could Not Be Found
  • A HATS For Many Occasions
  • What Are Your Application Priorities?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle