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
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.