Mysql Question posted in
The official MySQL documentation can be found here.

MySQL | Error Code: 1292. Truncated incorrect DOUBLE value: '149.89'

From sourcetable "amazon_order_history_2022" and column "Betrag" (datatype: varchar) I want to calculate a new column, that classifies data from column "Betrag", wether it smaller/equal to 100 or bigger than 100. So far the code is working.

Then I want to create a new table, that contains the result of above calculation and that is where "Error Code: 1292. Truncated incorrect DOUBLE value: ‘149.89’ " occurs.

DROP Table IF EXISTS SamyDB.synthese;
CREATE TABLE SamyDB.synthese AS
With temp1 as (
Select
Betrag
,replace(Betrag,'€ ','') as Betrag_clean
from amazon_order_history_2022
)
, temp2 as(
SELECT
Betrag
,Betrag_clean
,case 
    when cast(Betrag_clean AS DOUBLE) <= 100 then 'small'
    when cast(Betrag_clean AS DOUBLE) > 100 then 'big'
end as 'spent_temp'
FROM temp1
)
select *
from temp2

enter image description here

I was expecting to see the result of "select" calculation in the new table (see screenshot), but instead I receive "Error Code: 1292. Truncated incorrect DOUBLE value: ‘149.89’ ", when running the code with the "create table" statement.

Can anybody please support me?
Thank You very much in advance.

enter image description here

Tags:

2

Answers


  1. 0

    I suppose the error is due to using "," instead of "." in your input data:
    In the image there is "149,89" – you have to convert it to "149.89" before casting to double.
    Strangely, in your error message "Error Code: 1292. Truncated incorrect DOUBLE value: ‘149.89’" the conversion seems to be done…

    Login or Signup to reply.
  2. 0

    You have a comma ‘,’ inside your value and it thusly cannot convert it to a proper DOUBLE value. Simply replace the , with a . as well and it should work:

    DROP TABLE synthese;
CREATE TABLE synthese AS
With temp1 as (
Select
Betrag
,replace(replace(Betrag,'€ ',''), ',', '.') as Betrag_clean
from amazon_order_history_2022
)
, temp2 as(
SELECT
Betrag
,Betrag_clean
,case 
    when cast(Betrag_clean AS DOUBLE) <= 100 then 'small'
    when cast(Betrag_clean AS DOUBLE) > 100 then 'big'
end as 'spent_temp'
FROM temp1
)
select *
from temp2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search