* dan orlic
> INSERT INTO cp.Items SELECT distinct g.RecordID as id,'' as
> category_id, '' as pattern_id,'' as manufacturer_id, g.Item + g.Desc1
> + g.Desc2 + g.Desc3 as description, g.Desc4 as price,0 as quantity, ''
> as comments,'Active' as status,'n' AS is_bridal, 'Gallery' AS type,
> now() as created, now() as last_modified FROM copperlamp.Items g order
> by g.RecordID asc;
>
> and it does not error out, but there are problems with this... for
> example:
> ... g.Desc4 as price ... g.Desc4 ($500.00) is a varchar and price is a
> BigDecimal(10.2). but when it gets inserted the value is 0.00 for
> every field....
The string "$500.00" is easily identified as a price for a human, but mysql
don't know that "$" means money. In general mysql will try to convert a
string to a number if the string is used in a numeric context, but a string
starting with "$" is not identified as a number:
mysql> select "$500.00"+0,"500.00"+0,mid("$500.00",2)+0;
+-------------+------------+--------------------+
| "$500.00"+0 | "500.00"+0 | mid("$500.00",2)+0 |
+-------------+------------+--------------------+
| 0 | 500 | 500 |
+-------------+------------+--------------------+
1 row in set (0.00 sec)
You could use MID(g.Desc4,2) in your statement to make mysql ignore the "$"
character.
> that's one the other is:
> ...g.Item + g.Desc1 + g.Desc2 + g.Desc3 as description ... but though
> they are all varchars i can't seem to incorporate all the values into
> that one field. any thoughts would be great.
See the CONCAT() function:
<URL: http://dev.mysql.com/doc/mysql/en/String_functions.html#IDX1246 >
CONCAT(g.Item,g.Desc1,g.Desc2,g.Desc3) as description
Often one would like a space between the columns that are concatenated, in
that case CONCAT_WS() is what you want:
CONCAT_WS(' ',g.Item,g.Desc1,g.Desc2,g.Desc3) as description
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
