Convert and Expand Field through SQL/400

5 pts.
Tags:
AS/400
SQL
I am using the AS400 platform and a novice with SQL. I have a common field in multiple files - some files define the field as numeric and some alpha. My question: Is there a way in SQL to first convert the numeric field to alpha and then expand the alpha field adding two leading zeros? If this is possible, could it be done on the "live" files to avoid having to delete and then rebuild associated logicals?

This is a project with a lot of files to update and currently we are using the CHGPF command to expand the field and then using a COBOL program to either convert the numeric to alpha or move leading zeros to the existing alpha fields. Any ideas would be appreciated. Thanks.



Software/Hardware used:
as400
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Yes, you can use SQL to convert numeric to alpha.
You need to use the CAST operand.
This instruction will take a 4 digit number and convert it to alpha and put in leading zeros.
repeat(”0”,4 – length( cast(Aagt as varchar (4)))) concat (cast(aagt as varchar (4)))
If you want to increares the length of the field you just concat ’00’ to the front of the field.
*
No, you can not do this over a “live” file.
Since you are changing a field in the file you will need to create the new file and then recompile all your programs that use that file. Then you can run the SQL statement to “move” the data from the old file to the new file.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    If you want to alter the database structure, it can't be done directly against the current tables. SQL will block attempts to alter numeric columns to character and vice versa. But, a SQL VIEW can present numeric columns as character and vice versa. This could let you create VIEWs that present all of the columns in a common format. Once you are satisfied with the format, you can create new tables with the desired format and populate it with the data from the original tables. Existing programming probably has programming that does these conversions. That programming would need to be changed to honor the new formats. Other conversions can be done against live tables when they data types allow it. For example, you change a CHAR(8) column to CHAR(10) or you can change a CHAR() to a VARCHAR(). You can't change both length and datatype in a single operation; it would take two ALTERs to go from CHAR(10) to VARCHAR(20) for example. Another way to CAST a 4-digit NUMERIC to CHAR(4) with leading zeroes: 
    CAST(  RIGHT('0000' CONCAT VARCHAR(numeric_col),4)) AS CHAR(4)) AS NewChar4
    Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: