Common Sense Comparisons

Published: May 17, 2006

Hey, Esteemed, Professional Colleagues:

Today's technical tip is not rocket science, but that's OK with me because rocket science won't help me keep the factory going. No, today's tip is a practical, yet very simple, SQL (and OPNQRYF) technique that I hope to use regularly for years to come.

One of the first things that was drilled into my bald head in my early days of computer programming was that you can't compare numeric data to character data. It didn't matter what the language was--RPG II, COBOL 68, FORTRAN IV, GW-BASIC--numeric and character were apples and oranges.

I never questioned this restriction until I started working in a production environment. That's when I found out that a certain datum--a date, an item number, or a department code, for instance--might be stored in a numeric field in one file but in an alpha field in another file. Comparing fields of different types required me to convert one of the fields to the type of the other. Why couldn't the computer do that conversion for me?

In V5R3, IBM loosened up this restriction in certain places. I've discovered two of those places--SQL and Open Query File (OPNQRYF). It is possible as of V5R3 to compare numeric data to character data, with the computer handling the conversion. The rub is that the alpha field must contain valid numeric data.

As a simple example, consider a file with two fields--a four-byte alpha field cleverly named ALPHA and a five-digit numeric field of zero decimal places named NUM. The following query uses the DECIMAL function to illustrate what the system will and will not like in character-to-numeric comparisons.

select num, alpha, dec (alpha,4,0)
from some file

If I execute this query in interactive SQL, this is what I see.

NUM     ALPHA      DEC 
  3-    -3           3-
  3-    - 3      ++++++
  3-    -  3     ++++++
  3-    3-       ++++++
  0              ++++++
  1     1            1 
  2      2           2 
  3     +3           3 
  4     0004         4 
  4     0  4     ++++++
  5     5.0          5 
 40     4 0      ++++++

The lines with the plus signs in the third column show which character values will not convert to numeric during a character-to-numeric comparison. Notice what the system likes. Leading and trailing blanks are ignored, but embedded ones are not. You may include a leading plus sign or minus sign, and you may include a decimal point.

So far I have used character-to-numeric comparisons in two SQL clauses--WHERE and JOIN--and they work as I expected.

select *
   from SomeFile
   where CharField > NumField

OPNQRYF also allows comparison of character to numeric data. The following commands select the records where an alpha field is equal to a numeric field.

OPNQRYF FILE((ONEFILE))
   QRYSLT('alpha *eq num') 
CPYFRMQRYF FROMOPNID(ONEFILE) TOFILE(ANOTHER) +
   CRTFILE(*YES)

However, Query/400 does not allow character-to-numeric comparisons, at least not in V5R3.

--Ted