Re: your mail

[Mario Insigne <minsigne@CCSF.CC.CA.US>]

Lorene,
You can use this one. I think this is a lot easier to use.

let #amt = #amt * power(10, -2)
if $sign != '+'
  let #amt = #amt * -1
end-if

Mario
City College of San Francisco



On Wed, 19 Nov 1997, Lorene Hales wrote:

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