• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Making Sense of Codes

    October 9, 2017 Ted Holt

    I have most likely never seen your database, yet I can tell you with confidence that it is full of codes. We can’t live without them. Codes give us shortcuts for all sorts of types and categories. They consume less storage than the values they represent. They help us keep the database clean and consistent within itself.

    But they surely can be hard to read. Some codes are obvious. M for male and F for female, for instance. My experience is that most are not so. Look at this and see how much sense you can make of it.

    select d.ID, d.Name,
           d.Breed, d.Sex, d.Size
      from dogs as d
    
    ID Name Breed Sex Size
    1 Fifi 503 2 3
    2 Fido 408 1 2
    3 Lassie 810 2 2

    Time and time again, I have been amazed and impressed that the people I have served were able to work from reports and screens like this. How human beings can memorize so many code values and account numbers is astonishing.

    Nevertheless, adding clarity when required is not difficult, especially if you use SQL.

    The best way (in my opinion, of course) to explain codes is to create lookup tables. In this example, you could create a table with American Kennel Club breed codes and descriptions.

    create table breeds
      ( ID  integer primary key,
        Name  varchar(24) );
    
    insert into breeds values
       ( 108, 'Labrador retriever'),
       ( 408, 'Dachshund'),
       ( 503, 'Chihuahua'),
       ( 810, 'Collie')
    

    You can join the lookup table to retrieve the breed description.

    select d.ID, d.Name,
           d.Breed, b.Name,
           d.Sex, d.Group, d.Size, d.Coat
      from dogs as d
      left join breeds as b
        on d.breed = b.ID
    
    ID Name Breed Sex Size
    1 Fifi 503 Chihuahua 2 3
    2 Fido 408 Dachshund 1 2
    3 Lassie 810 Collie 2 2

    But what if you don’t have a lookup table and aren’t allowed to create one? What if there are only a few values? What if there are only two?

    One alternative to a lookup table is the CASE statement. This is an easy way to explain code values. Here’s an example:

    select d.ID, d.Name,
           d.Breed, b.Name,
           case d.Sex
              when 1 then '1=Dog'
              when 2 then '2=Bitch'
              else char(d.Sex) concat '=Unknown'
           end as Sex,
           case d.Size
              when 1 then '1=Miniature'
              when 2 then '2=Standard'
              when 3 then '3=Toy'
              else char(d.Size) concat '=Unknown'
           end as Size
      from dogs as d
      left join breeds as b
        on d.breed = b.ID
    
    ID Name Breed Sex Size
    1 Fifi 503 Chihuahua 2=Bitch 3=Toy
    2 Fido 408 Dachshund 1=Dog 2=Standard
    3 Lassie 810 Collie 2=Bitch 2=Standard

    I could have put the sex and size descriptions in columns of their own. That’s what I usually do. But I like this format too. Either way, showing the code itself and the meaning of the code gives the user all pertinent information.

    Another way to describe codes without permanent database tables is to use common table expressions. This technique lets you build descriptions “on the fly.” This query uses common table expressions to interpret sex and size codes.

    with Sexes as
       ( select *
           from ( values (1, 'Dog'), (2, 'Bitch')
                 ) as sx (ID, Description) ),
         Sizes as
       ( select *
           from ( values (1, 'Miniature'),
                         (2, 'Standard'),
                         (3, 'Toy')
                 ) as sz (ID, Description) )
    
    select d.ID, d.Name,
           d.Breed, b.Name,
           d.Sex, x.Description, 
           d.Size, z.Description
      from dogs as d
      left join breeds as b
        on d.breed = b.ID
      left join Sexes as x
        on d.Sex = x.ID
      left join Sizes as z
        on d.Size = z.ID
    
    ID Name Breed Sex Size
    1 Fifi 503 Chihuahua 2 Bitch 3 Toy
    2 Fido 408 Dachshund 1 Dog 2 Standard
    3 Lassie 810 Collie 2 Bitch 2 Standard

     

    One technique that database gurus generally frown upon is the One True Lookup Table (OTLT), which is a table of miscellaneous values. Here’s an example:

    KEYTYPE       KEYVALUE   DATA
    -----------   ---------  -----------------------
    SEX           M          Male
    SEX           F          Female
    COLOR         1          Red
    COLOR         2          Blue
    COLOR         3          Green
    COLOR         4          Yellow
    COLOR         5          Purple
    TSHIRTSIZE    XS         Extra small
    TSHIRTSIZE    S          Small
    TSHIRTSIZE    M          Medium
    TSHIRTSIZE    L          Large
    TSHIRTSIZE    XL         Extra large
    TSHIRTSIZE    XXL        Extra extra large
    DRESSSIZE     2          Small
    DRESSSIZE     4          Small
    DRESSSIZE     6          Medium
    DRESSSIZE     8          Medium
    DRESSSIZE     10         Large
    ITEMTYPE      1          Finished good
    ITEMTYPE      2          Assembly
    ITEMTYPE      3          Purchased finished good
    ITEMTYPE      4          Raw material
    

    Joining to such a table means not only joining on the proper code value, but on the value type as well. Such a table was probably not a bad design in the pre-SQL years, when reference data was accessed through random reads. Using such a table in an SQL query leads to all sorts of problems, which I will not address here. If this topic interests you, you can find plenty of discussion of OTLT on the Web.

    What’s the best method for defining codes? I recommend creating lookup tables, even for only a few values. Yes, it means that you may have a lot of itsy-bitsy tables in the database, but relational databases are all about tables, and SQL handles them well. If a lookup table is out of the question, CASE and common table expressions provide a good alternative. The downside to these latter two techniques, however, is that you may have a lot of queries to modify when your organization defines another code value.

    Regardless of how we interpret codes, we have the power to clarify data for the benefit of the people we serve. Let’s make life as easy for them as we can.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Four Hundred Guru, Guru, IBM i, SQL

    Sponsored by
    Midrange Dynamics North America

    Git up to speed with MDChange!

    Git can be lightning-fast when dealing with just a few hundred items in a repository. But when dealing with tens of thousands of items, transaction wait times can take minutes.

    MDChange offers an elegant solution that enables you to work efficiently any size Git repository while making your Git experience seamless and highly responsive.

    Learn more.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Free Speech in Restricted Times Power9 Big Iron “Fleetwood/Mack” Rumors

    3 thoughts on “Guru: Making Sense of Codes”

    • Roger Harman says:
      October 9, 2017 at 1:29 pm

      Although the overhead can be increased, I have used a UDF for a number of these lookups. The UDF eliminates the multiple joins and is easily read/understood.

      For individual lookup tables:
      Select ShirtStyle, getShirtColor(ShirtColorCode) from ShirtFile.

      This works for your One True Lookup Table as mentioned. Just pass the key type.
      Select ShirtStyle, getLookupValue(‘SHIRTCOLOR’, ShirtColorCode) from ShirtFile.

      Reply
    • Brian Rusch says:
      October 9, 2017 at 3:02 pm

      Hi Ted,

      Unless I’m missing something, using one table for all the codes (OTLT) should not be any more difficult using SQL than any other method. You could use common table expressions to subset the lookup table:

      With Breeds as
      ( select keyvalue as ID, data as Name
      from LookupTable where keytype = ‘BREED’ ),
      Sexes as
      ( select keyvalue as ID, data as Description
      from LookupTable where keytype = ‘SEX’ ),
      Sizes as
      ( select keyvalue as ID, data as Description
      from LookupTable where keytype = ‘SIZE’ )

      select d.ID, d.Name,
      d.Breed, b.Name,
      d.Sex, x.Description,
      d.Size, z.Description
      from dogs as d
      left join breeds as b
      on d.breed = b.ID
      left join Sexes as x
      on d.Sex = x.ID
      left join Sizes as z
      on d.Size = z.ID

      Reply
    • Ted Holt says:
      November 13, 2017 at 4:12 pm

      Joining to a OTLT is not impossible, just messy. Consider that all the key values have to be character, so joining to a table with numeric keys always requires conversion. You can find plenty of pros and cons on the Web.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 27 Issue: 65

This Issue Sponsored By

  • Fresche Solutions
  • Linoma Software
  • T.L. Ashford
  • Computer Keyes
  • WorksRight Software

Table of Contents

  • RDi Jumps Aboard The TR Train
  • Power9 Big Iron “Fleetwood/Mack” Rumors
  • Guru: Making Sense of Codes
  • As I See It: Free Speech in Restricted Times
  • Cost, Simplicity Driving Cloud HA Adoption

Content archive

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

Recent Posts

  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20
  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, 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 © 2025 IT Jungle