Alternatives To SQL Literals

Published: October 3, 2012

by 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

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

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

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

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