• 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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions.

    Upgrading to Power10, Power11, or cloud hosted system, Maxava has you covered!

    Book A Consultation Today

    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

  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26
  • 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

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