Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    4

    Question 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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    PHP Code:
    SELECT Replace(Replace(Str(35.25132), '.'''), ' ''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

  3. #3
    Join Date
    Apr 2004
    Posts
    4

    Unhappy

    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?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  5. #5
    Join Date
    Apr 2004
    Posts
    4

    Unhappy

    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....

  6. #6
    Join Date
    Apr 2004
    Posts
    4

    Post

    Oh, BTW, I'm using SQL Server 2000 and Embarcadero DBArtisan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •