I am creating several tables for one big join, everything works except for one table which I cannot create. There is a column in one initial table that contained Integers of temperature in °C in each cell (e.g. 4 °C, 6°C etc. ). So what i did was that I got rid of °C symbol so that only Integers are left in this columns since I needed to avg the numbers etc. When I am creating a Select or a View everything works fine. But when I try to create a Table I am receiving following message:" SQL Error [1292] [22007]: (conn=41) Truncated incorrect INTEGER value: ‘4 °c’ ". And this is strange because I do not have any °C symbol in the cells next to the Integers. Therefore, I can create a view but I cannot create a table. Anyone can think of any reason? Please take a look of a View for that some guys helped me with on this forum here.
CREATE OR REPLACE VIEW v_weather_data AS
SELECT
w.date,
w.time,
w.city,
c.country,
CONVERT (REPLACE(w.temp, ' °c', ''), INT) AS 'daily_avg_temp_°c',
CONVERT (REPLACE(w.rain, ' mm', ''), DECIMAL) AS 'rainy_hours',
CONVERT (REPLACE(w.temp, ' km/h', ''), INT) AS 'max_wind_gust_km_h'
FROM countries c
JOIN
weather w
ON
c.capital_city = w.city
WHERE
w.city IS NOT NULL
ORDER BY w.date DESC
