Convert and Expand Field through SQL/400
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
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:
October 9, 2009 4:37 PM
UPDATED:
October 12, 2009 11:45 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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 NewChar4Tom