>It appears that mysys 4.1 does not know how to multiply a dollar
>amount to another number. Has anyone else seen this problem?
If price is a string beginning with '$', MySQL will autoconvert
SubString(price,2) to a numeric iif it's in a numeric expression, but
you'd be much better off losing the leading '$' chars and converting the
column to Decimal.
PB
Karl Larsen wrote:
> I'm trying to multiply numbers one of which is money. The money
> looks like this:
>
> SELECT price FROM titles;
>
> | price |
> +--------+
> | $20.00 |
> | $19.99 |
> | $7.99 |
> | $19.99 |
> | $11.95 |
> | $19.99 |
> | $14.99 |
> | $11.95 |
> | $22.95 |
> | $2.99 |
> | $10.95 |
> | $7.00 |
> | $2.99 |
> | $20.95 |
> | NULL |
> | $19.99 |
> | $21.59 |
> | NULL |
> +--------+
> 18 rows in set (0.01 sec)
>
> When I use SELECT title_id, ytd_sales * price From titles;
>
> I get:
> | title_id | ytd_sales | price * ytd_sales |
> +----------+-----------+-------------------+
> | PC8888 | 4095 | 0 |
> | BU1032 | 4095 | 0 |
> | PS7777 | 3336 | 0 |
> | PS3333 | 4072 | 0 |
> | BU1111 | 3876 | 0 |
> | MC2222 | 2032 | 0 |
> | TC7777 | 4095 | 0 |
> | TC4203 | 15096 | 0 |
> | PC1035 | 8780 | 0 |
> | BU2075 | 18722 | 0 |
> | PS2091 | 2045 | 0 |
> | PS2106 | 111 | 0 |
> | MC3021 | 22246 | 0 |
> | TC3218 | 375 | 0 |
> | MC3026 | NULL | NULL |
> | BU7832 | 4095 | 0 |
> | PS1372 | 375 | 0 |
> | PC9999 | NULL | NULL |
> +----------+-----------+-------------------+
> 18 rows in set (0.04 sec)
>
> It appears that mysys 4.1 does not know how to multiply a dollar
> amount to another number. Has anyone else seen this problem?
>
> Karl
>
>
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.9.2/372 - Release Date: 6/21/2006
