Re: Number Decimal Position

[Scott Gunter <ksgunter@TDYRYAN.COM>]

Lorene,

I'm new to SQR. Thus, I'm not sure why your conversion didn't work.
However, while reading your note an alternative did come to mind.

In scenario 1 after #Amount has been given a value you could add this
statement:

               Divide 100 into #Amount

Or have you looked at using an edit?

               i.e. let #amount = edit($amount,'09999999.99')

I haven't had time to test this maybe you can try it out.

Scott Gunter
Business Analyst Sr
Teledyne Ryan Aeronautical
ksgunter@tdyryan.com


At 02:58 PM 11/19/97 -0800, you 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
>
>
Scott Gunter
IS Representative SR
Teledyne Ryan Aeronautical
619/291-7311 x1741