• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Alternatives To SQL Literals

    October 3, 2012 Ted Holt

    Literals have caused me grief on more than one occasion. Trying to figure out what a certain number or character literal means in a program has wasted too much of my time, and my time is more valuable to me than money. I have written about this topic before, but I have not talked about literals in SQL.

    The example I’ve chosen may not be the best one, since the ratio of pounds to kilograms never changes. I chose it because it’s one most people should be able to relate to. The article I just referred to covers the arguments for and against unchanging literals, so I won’t rehash them here.

    Suppose we work for a factory in the United States that ships products to other countries. The factory uses customary American units of measurement, but documents that go to other countries require metric measurements.

    Here’s an item master table to start our example.

    create table mylib/items
      (item char(7),
       description char(15),
       weight dec(7,2),
       primary key (item))
    
    insert into mylib/items
    values ('ABC1000', 'Widget 6-inch', 1.2),
           ('ABC1010', 'Widget 12-inch', 2.4),
           ('ABC1020', 'Widget 18-inch', 3.6),
           ('BR-549', 'Samples', 280)
    

    The weight column refers to pounds. To convert pounds to kilograms is a matter of simple division.

    select i.item, i.description, i.weight, 
           dec(round(i.weight / 2.2046,3),7,3) as ShipWeight
      from mylib/items as i
    




    ITEM

    ITEM

    DESCRIPTION

    WEIGHT

    SHIPWEIGHT

    ABC1000

    Widget 6-inch

    1.20

    0.544

    ABC1010

    Widget 12-inch

    2.40

    1.089

    ABC1020

    Widget 18-inch

    3.60

    1.633

    BR-549

    Samples

    280.00

    127.007

    If you wish to replace the literal 2.2046 with something self-documenting, here’s one method.

    create function mylib/LbsPerKG ()
    returns double
    language sql
    return 2.2046
    

    The LbsToKG (pounds to kilograms) function returns the literal value it replaces. The division looks like this:

    select i.item, i.description, i.weight, 
           dec(round(i.weight / LbsPerKG(),3),7,3) as ShipWeight
      from items as i
    

    A second method is to write a function that does the conversion. (I prefer this one to the last one.) Here’s a function that converts pounds to kilograms.

    create function mylib/LbsToKG ( inPounds double)
    returns double
    language sql
    return inPounds / 2.2046
    

    And here’s the rewritten query.

    select i.item, i.description, i.weight, 
           dec(round(LbsToKG(i.weight),3),7,3) as ShipWeight
      from items as i
    

    Another technique you might find helpful is to use global variables. For more information, read Michael Sansoterra’s excellent article New in DB2 for i 7.1: Use Global Variables to Track Environment Settings.

    If you have a will to remove a literal from SQL queries, there is more than one way.

    RELATED STORIES

    New in DB2 for i 7.1: Use Global Variables to Track Environment Settings

    Use Named Constants to Write Clearer Code



                         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
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Sirius Computer Solutions:  A comprehensive, cost-effective cloud solution for IBM i users
    Tributary Systems:  Storage Director® makes your tape work better. FREE Webinar and PDF
    System i Developer:  RPG & DB2 Summit, Oct 23-25 in Minneapolis. Register by Oct 12 to save $100!

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Some Things To Ponder On The Impending Power7+ Era Power7+ Launches In Multi-Chassis Power 770+ And 780+ Systems

    Leave a Reply Cancel reply

Volume 12, Number 24 -- October 3, 2012
THIS ISSUE SPONSORED BY:

Bug Busters Software Engineering
WorksRight Software
Connectria Hosting

Table of Contents

  • Debugging Server Jobs In Green Screen
  • Alternatives To SQL Literals
  • Admin Alert: Seven Things You Should Be Monitoring On Your System

Content archive

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

Recent Posts

  • 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
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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