I import my table from local to monetdblite, some columns of my data have some special observation value. For example, my colunme RET has 1000 numeric values, 10 missing data, and 2 special observation value which are filled with character C. When I import the data into MonetDBLite, all the values in this column are forced into character format. I need to convert this back to numeric value, I think I can replace all "C" with NA and then convert all values to numeric, but I got the error message:
Error in .local(conn, statement, ...) :
Unable to execute statement 'SELECT CAST("RET" AS NUMERIC) AS "RET"
FROM (SELECT CASE WHEN ("RET" = 'C') THEN (NULL) WHEN NOT("RE...'.
Server says 'SQLException:TYPE:42000!Decimal (B) doesn't have format (18.3)'.