• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Easier Overloading of SQL Functions

    June 14, 2016 Hey, Ted

    In Formatting Dates with SQL, Take 2, you have shared a great technique. Function overloading is a real boon to SQL programming. Here’s another way to handle the same issue with what I believe is less code and less invasive (meaning you won’t have to recompile programs that use the FMTDATE service program).

    Leave your original FMTDATE function alone, but add the following to the SQL source:

    create function xxx/fmtdate
    (inDate varchar(8), inFromFmt varchar(8), inToFmt varchar(8))
    returns varchar(10)
    language SQL
    specific FMTDATEA
    deterministic
    returns null on null input
    
    begin
     declare DateNum numeric (8,0);
     declare OutDate varchar(10);
     set DateNum = decimal(inDate);
     set OutDate = FmtDate(DateNum, inFromFmt, inToFmt);
     return OutDate
    end
    

    I’ve used this technique to overload lots of UDFs and keep the HLL programming simple.

    –Michael Quigley

    Once again I overlooked the obvious. I did more than the necessary amount of work to make FMTDATE accept character arguments, and I’m very grateful to Michael for pointing it out.

    I don’t think I’ll make that mistake again. From now on, I expect I’ll create new SQL functions without modifying the ones upon which they’re based. However, in the case of FMTDATE, I’ve decided not to use this technique because another reader gave me an even better idea. Look for take 3 of Formatting Dates with SQL, coming very soon in Four Hundred Guru!

    –Ted

    RELATED STORIES

    Ease the Burden of Overloading

    Formatting Dates with SQL, Take 2

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ProData

    Simple.  Reliable.  Powerful. 

    IBM i software to help you navigate your universe!

    Use these tools to get where you are going:

    DBU to easily, yet securely, access the data on your IBM i or remote system.
    RDB Connect for powerful record level access to remote data using RPG, CL or COBOL.

    SQL/Pro and Audit offer reliable SQL data processing, tracking and reporting.

    RDR retrieves those records which were deleted in error.

    And more!

    Join our DBUniverse of loyal, happy customers and experience your destination…

    Do IT with DBU!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    System i Developer:  RPG & DB2 Summit - October 4-6 2016 in Chicago. Register now!
    NGS:  Webinar: Answering Business Qs with Reporting & Analytics. June 16. RSVP!
    OCEAN:  3 days of inspiration! 2016 IBM i Technical Conference, July 21-23, Costa Mesa, California

    The Server Refresh Cycle Loses Steam The AS/400 At 28: A HENRY, Not A DINK

    Leave a Reply Cancel reply

Volume 16, Number 14 -- June 14, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
ProData Computer Services
System i Developer

Table of Contents

  • The Basics Of XML-SAX
  • Easier Overloading of SQL Functions
  • Inline Table Functions In DB2 For i

Content archive

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

Recent Posts

  • IBM i Development and Modernization is Getting A Fresche Start with Some Ground-Breaking Subscriptions
  • CloudSAFE And Focal Point Solutions Group Combine Services, Unify Brands
  • Guru: Partitioning Result Sets Using SQL
  • As I See It: Elusive Connections
  • IBM i PTF Guide, Volume 25, Number 47
  • AWS Inks Deal With Connectria To Have a Power Play
  • IBM i Shops Have Alternatives to Db2 Web Query
  • Eradani Lays Waste to API Payload Restrictions
  • Four Hundred Monitor, November 15
  • Old PHP and Other PASE Apps Break on IBM i 7.5

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