Re: "convert" problem

[Nathan Treadway <nathant@ONTKO.COM>]

Lorene Hales wrote:
>                                 SCENARIO 1
[...]
>      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).
[...]
>                                 SCENARIO 2
[...]
>                 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
[...]
>                 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:
[...]
>                 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.

First, one question: in Scenario 3, did you have the "convert" in your SQL
statement?

I haven't used the Sybase "money" datetype much, but if the answer to my
question is "yes", then I think I can explain your results.

One thing to note is that "money" is a Sybase datatype, not an SQR one.
Thus, the issue is not how SQR converts the field, but how Sysbase does. SQR
just deals with numbers (# variables) and text ($ variables).  When SQR
sends it data, Sybase treats these two as "number" and "varchar" data types,
I believe.

Sybase has some rules about how values can be converted from one datatype to
another.  I forget which manual has it, but somewhere in a section on
datatypes there is a table showing which datatypes can be implicitely
converted (no "convert") to other datatypes, which conversions must be done
explicitely (using a "convert"), and which can never be performed.

Converting from "varchar" to "money" requires and explicit conversion, which
is why you were not allowed to insert $amount_out into the "money" column
in Scenario 2.

I'm guessing that you did have the "convert" when you tried Scenario 3,
which is why it worked.

As you guessed (and other posts to this list note), SQR doesn't have any
concept of implied decimal, so you'll either have to use a hard-coded
decimal or do some math on the implied version.  In either case, you can
then
   a) leave the value in #amount and insert #amount directly into the
Money column (without a "convert"), or
   b) move #amount to amount_out and use "convert"

a) should work because "number" to "money" conversions can be done
implicitly; b) should work because "varchar" to "money" conversions can be
done explicitly.  Note that SQR can do a sort of implicit conversion between
"string" and "number" (as in "move #amount to $amount_out"), while Sybase
does not do implicit conversion from "varchar" to "money".


If you did not in fact have a "convert" in Scenario 3, I'm not sure what's
up.  Hopefully some of this info will be helpful anyway.

                                                Nathan
----------------------------------------------------------------------------
Nathan Treadway    | Ray Ontko & Co. | info@ontko.com (auto-reply server)
nathant@ontko.com  | Richmond, IN    | ftp.ontko.com, http://www.ontko.com/