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:SELECT Replace(Replace(Str(35.25, 13, 2), '.', ''), ' ', '0')If this doesn't help, post again with more details, and I bet one of the bright folks here will be able to help.
-PatP|||The replace did not work any better than the replicate function.
Here is some more info...
If I declare a variable as binary and then fill it in the following manner I get the data as I want it...
declare @.amt binary(6)
select @.amt = 0x000000003525
but, I need to convert my field "disbursement_amt" (defined as numeric (15,2) )off table "d" to binary...and I can't figure out how to do something similar to:
select @.amt = 0x<d.disbursement_amt)>
...any more help?|||Ok, my code works just fine using SQL Server 2000 and Microsoft Query Analyzer (I just tested it), what are you using (engine and client)?
-PatP|||Your code does work!...but that's not the problem...see how I used it below
declare @.field numeric(15,2)
select @.field = '35.25'
select @.field
declare @.outnum char(12)
SELECT @.outnum = Replace(Replace(Str(@.field, 13, 2), '.', ''), ' ', '0')
select @.outnum
...Great if I need the output in character format, but I need the data output (@.outnum) in binary format. CONVERT() does not do it...|||Oh, BTW, I'm using SQL Server 2000 and Embarcadero DBArtisan
