• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • SQL Cross Platform Interoperability: The Proper Function

    February 7, 2007 Michael Sansoterra

    The code accompanying this article is available for download

    This tip has one very easy point: There’s lot of reusable SQL code out there, and System i (AS/400) DB2 developers can take advantage of it. Even though SQL’s promise of cross platform independence has never materialized, there are still enough similarities to make conversion between platforms worthwhile. All it takes is a little practice.

    This tip assumes you have some knowledge of writing SQL modules (triggers, functions or stored procedures in the SQL language). If you’re new to this arena, review the Control State section in the IBM SQL Reference guide to get a handle on the basic elements of programming entirely in SQL. SQL control statements are simply statements (as in any other computer language) that control the flow of logic in a program: conditional and looping statements, error handling, etc.

    The seeds for this tip started a few years ago when I was working on a SQL Server project. I was merging address data from two databases into one. Unfortunately, one database contained mixed case (actually “proper” case) information and the other uppercase information. The customer, of course, wanted to have everything in the resulting database in proper case.

    Proper case is similar to title case in that the first letter in each word is capitalized as shown here:

    This Sentence Is Written In Proper Case.

    THIS SENTENCE IS WRITTEN IN UPPER CASE.

    It was time to go home, but I was able to wow the customer by having the case conversion done in a manner of minutes. How did I do that? Easy, I searched the Internet and found a pre-written user-defined function (written in SQL Server’s T-SQL) that converted a text string to proper case. Problem solved.

    In case you’re not familiar with user-defined functions, they’re basically little pieces of user-written code that can accept zero or more inputs, perform logic on those inputs and return a single output (similar to an RPG subprocedure). Here’s an example of how to use the Proper user-defined function in SQL Server:

    Select Proper(‘2431 BROADWAY AVE’)

    Returns:

    2431 Broadway Ave

    Just a little while ago, I had a similar issue come up for a DB2 database. I needed to keep names in proper case in an employee table instead of upper case. I knew a scalar UDF to convert a string to proper case would do the trick again. My first thought was to either find one written for DB2 or to write one from scratch. But why reinvent the wheel? I whipped out the SQL Server version and in under 15 minutes I had the routine converted from T-SQL to DB2 SQL. (It would have gone even quicker except I first decided to test with DB2 for Windows, which always manages to cause me to stumble at least once per session!)

    The code accompanying this article contains the DB2 code for user-defined function “Proper”. Unfortunately, for the sake of giving credit to the original T-SQL author, I don’t know who wrote the original code, but I did manage to find a Web site that still publishes the example. For reference and comparison, click here to see the original T-SQL version of the code.

    Here are a few of the things that were done to convert the code:

    • Code lines were delimited with semi-colons
    • The IF conditional and WHILE looping structures were changed
    • I specified some required DB2-specific clauses
    • I made a few other minor changes, such as substituting the LENGTH DB2 built-in function for the LEN T-SQL built in function

    When working with DB2’s modules, do not limit your Internet searches for DB2 code. You can start looking for DB2 specific code, but if you can’t find it, someone has probably written code for another database platform that can easily be ported to DB2. As a developer, the best thing about the Internet for me has been code samples and re-useable code. Hey, why do the work if someone else has already done it (or most of it) for you?

    Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    VISUAL LANSA 16 WEBINAR

    Trying to balance stability and agility in your IBM i environment?

    Join this webinar and explore Visual LANSA 16 – our enhanced professional low-code platform designed to help organizations running on IBM i evolve seamlessly for what’s next.

    🎙️VISUAL LANSA 16 WEBINAR

    Break Monolithic IBM i Applications and Unlock New Value

    Explore modernization without rewriting. Decouple monolithic applications and extend their value through integration with modern services, web frameworks, and cloud technologies.

    🗓️ July 10, 2025

    ⏰ 9 AM – 10 AM CDT (4 PM to 5 PM CEST)

    See the webinar schedule in your time zone

    Register to join the webinar now

    What to Expect

    • Get to know Visual LANSA 16, its core features, latest enhancements, and use cases
    • Understand how you can transition to a MACH-aligned architecture to enable faster innovation
    • Discover native REST APIs, WebView2 support, cloud-ready Azure licensing, and more to help transform and scale your IBM i applications

    Read more about V16 here.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Aldon:  ALM solutions to accelerate your application development
    COMMON:  Join us at the 2007 conference, April 29 – May 3, in Anaheim, California
    Maximum Availabilty:  Secure, cost-effect, real-time iSeries replication software

    Books on Sale at the IT Jungle Store: 30 Percent Off for 30 Days

    The System i Pocket RPG & RPG IV Guide: List Price, $69.95; Sale Price, $49.00
    The iSeries Pocket Database Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Developers' Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket SQL Guide: List Price, $59.00; Sale Price, $41.00
    The iSeries Pocket Query Guide: List Price, $49.00; Sale Price, $34.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00; Sale Price, $27.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    iSeries Express Web Implementer's Guide: List Price, $59.00; Sale Price, $41.00
    Getting Started with WebSphere Development Studio for iSeries: List Price, $79.95; Sale Price, $56.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00; Sale Price, $62.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00; Sale Price, $34.00
    WebFacing Application Design and Development Guide: List Price, $55.00; Sale Price, $38.00
    Can the AS/400 Survive IBM?: List Price, $49.00; Sale Price, $34.00
    The All-Everything Machine: List Price, $29.95; Sale Price, $21.00
    Chip Wars: List Price, $29.95; Sale Price, $21.00

    Bottomline Hooks Into J.D. Edwards Via Oracle Fusion Middleware Faster i5 595 Rumored to Be Imminent

    Leave a Reply Cancel reply

Volume 7, Number 5 -- February 7, 2007
THIS ISSUE SPONSORED BY:

WorksRight Software
SEQUEL
RPG & DB2 Summit

Table of Contents

  • Opportunities, Not Problems!
  • SQL Cross Platform Interoperability: The Proper Function
  • Admin Alert: Selectively Sending Break Messages to Active Users

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