Alpha to Numeric with Query/400

Hey, Ted:

In the January 29, 2003, issue of Midrange Guru, you provided some ways to convert an alphabetic field to numeric. Can you tell me how to do such a conversion in Query/400?

-- Camille in Canada

The usual way to make Query convert alpha to numeric is with the microsecond function.

For example, suppose you have a two-byte alpha field with a right-adjusted, zero-filled month number.

You can convert the alpha value to a number like this:

I suppose this is cute, but I don't like this method and avoid using it in production. The microsecond function cannot handle more than six digits. The number has to be right-adjusted and zero-filled. I think this method is just plain goofy.

To introduce the method I prefer, here's a question from a reader in Italy:

What do you think about using the SQL CAST operation to convert alpha values to numerics?

-- Rossano

I think SQL is a good way to go. Query/400 can read SQL views in the same way it reads logical files.

The following SQL command creates a view that defines the alpha field amount as a nine-digit packed-decimal field with two decimal positions:

create view myview as 
    select key, dec(amount,9,2) as amt from mydata

The following SQL commands convert alpha fields to numeric values:

select real(amount) from mydata
select double(amount) from mydata 
select int(month) from mydata    
select cast (amount as double) from mydata

Not only is this good for Query, you could use SQL statements like these in RPG programs. See "Reader Feedback and Insights: Overlooking the Obvious" in the November 8, 2002, issue of Midrange Guru to learn how to use SQL functions in RPG programs.

-- Ted

Sponsored By WORKSRIGHT SOFTWARE

CASS Certification What is it? Why do you need it? CASS stands for "Coding Accuracy Support System." This test, developed by the U.S. Postal Service, determines whether ZIP Code software can accurately assign ZIP Codes to mailing addresses. WorksRight offers CASS-certified software intended to improve the accuracy of carrier route, 5-digit ZIP, ZIP+4, and delivery point codes. For more information or a 30-day free trial, visit www.worksright.com or call 601-856-8337.