Re: your mail

["Johnson, Kate" <JOHNSOK@BESTWESTERN.COM>]

Mario!!!

> ----------
> From:         Mario Insigne[SMTP:minsigne@CCSF.CC.CA.US]
> Reply To:     SQR-USERS@USA.NET
> Sent:         Thursday, November 20, 1997 9:48 AM
> To:   Multiple recipients of list SQR-USERS
> Subject:      Re: your mail
>
> 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
> >
>