• 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
    Chilli IT

    Chilli is one of the UKs leading IBM support and management providers with 20 years’ experience in the power and storage industry. Our bespoke solutions for maintenance, security and infrastructure delivers a service which is cost effective, increases productivity and enhances efficiency. Our ethical approach and unrivalled knowledge has secured business partnerships with blue-chip companies in the technology, retail, banking and travel sectors.

    As an IBM Business Partner, we provide you with the peace of mind that you are working in partnership with a company accredited to the highest standard. Our team of experts have worked together for many years and deliver projects which include consolidation, High Availability, Operating System upgrades; and backup and recovery installations.

    Contact us to see how we can help your business with IBM support and management.

    www.chilli-it.co.uk

    info@chilli–it.co.uk

    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

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.