Unanswered: Converting SQL numeric datatype to a mainframe (cobol) packed field
Hopefully I have the right forum for this questions....
I am trying to convert a field defined in a SQL database as NUMERIC (15,2) to something I can export to a mainframe. The field on the mainframe is a packed field that is 6 (alphanumeric)chars in length. Mainframe defines as PIC X(6) and redefines as PIC 9(10)V99 PACKED(uses each byte of the 2-byte char). Must be zero filled to the left. Should look like the following for an amount of $35.25: 000000003525
Pulling my hair out...can anyone help before I go bald?
A lot depends on how you are trying to export the data from the SQL database to the mainframe. If you use one of the ETL or data connector solutions, you should be able to simply "push" the data across with zero conversion effort.
If you are converting to a flat file to import into a mainframe system that isn't supported by ETL, you'll have to do the conversion on the SQL side because very few mainframe packages deal very well with "human formatted" data, which is about all current generations of SQL engines produce. The major problem here is that you are trying to get the SQL engine to format things in a way that they rarely use (I can't imagine a business user requesting data formatted as 9(10)V99).
Depending on which SQL engine you are using, the syntax will be slightly different. On the chance that you mean SQL Server, I'd use: