No Subject

[Lorene Hales <halesl@CCMAIL.ORST.EDU>]

     Hi,

     An interesting phenomenon has occurred (translate: this has been
     frustrating the h... out of me for 2 days). The intent is to port data
     from an Abacus database on a Wang to a table which will be used by
     PeopleSoft Financial System on an RS6000 with a Sybase platform.

                                SCENARIO 1

     The program reads an input record from a flat file with a numeric amount
     defined as:
                     EXT-AMOUNT  PIC 9(09)V99    ('V' is an implied decimal)

     In the program, this amount is loaded into the field -- #Amount:
            let #Amount = substr($LoadRecord,16,11)

     the sign, a separate field, is evaluated and a "-" added as necessary
            If $Sign != '+' let #Amount = -#Amount

     then it loads this field to yet another field:
            move #Amount to $Amount_out

     finally it loads this field, within an SQL statement, into the
     PeopleSoft table as:
            convert(money,$Amount_out).

                ORIGINAL DATA:   00000244817
                EXPECTED RESULT: 000002448.17
                ACTUAL RESULT:   00000244817.00


                                SCENARIO 2

     I redefined the field in the original input file to have a hard
     decimal in place:
                EXT-AMOUNT  PIC 9(09).99

     I changed the length to reflect the hard-coded decimal
                let #Amount = substr($LoadRecord,16,12)

     I ran the SQR load progam getting rid of the convert statement.
     Instead I just loaded the field containing the decimal
                $Amount_out

     This time I got the following error message:
         (SQR 5528) Sybase DBSQLEXEC error in cursor 1:
              (257) Implicit conversion from datatype 'VARCHAR' to
                   'DECIMAL' is not allowed. Use the CONVERT function to
                    run this query.


                                SCENARIO 3

     It was suggested that SQR might only recognize money as a field with a
     length of 14 characters.  So I changed the input file again to add two
     additional leading zeros while retaining the hard-coded decimal.
                EXT-AMOUNT  PIC 9(11).99

     I changed the length to reflect the new data
                let #Amount = substr($LoadRecord,16,14)

     This time I ran the SQR load program with the following results:

                ORIGINAL DATA:   00000002448.17
                EXPECTED RESULT: 00000002448.17
                ACTUAL RESULT:   00000002448.17

     Please, can someone explain to me why I had to have a 14-char field
     for SQR to convert the field to money...or if I missed some other way
     to resolve this problem.

     Thanks in advance,
     Lorene