MySQL query works as normal SELECT but not inside CREATE TABLE AS..?

Question

I'm trying to figure out why this query SELECTs just fine, but I can't use it to create a temporary table... The IF = '', 0 stuff didn't used to exist in the original query so I added it to make sure that I wasn't trying to sum anything that could potentially be an empty string, but it didn't fix the problem. This select works and returns the correct result set:

SELECT 
    SUM(((IF(reserve_transactions.Revenue = '',
        0,
        reserve_transactions.Revenue) * IF(reserve_transactions.Confidence = '',
        0,
        reserve_transactions.Confidence)) / 100)) AS rawadjusted
FROM
    (store_locations
    LEFT JOIN reserve_transactions ON ((store_locations.ID = location_sales)))
GROUP BY store_locations.Loc_Long

But this does not:

CREATE TEMPORARY TABLE tmptable_which_doesnt_work AS SELECT 
    SUM(((IF(reserve_transactions.Revenue = '',
        0,
        reserve_transactions.Revenue) * IF(reserve_transactions.Confidence = '',
        0,
        reserve_transactions.Confidence)) / 100)) AS rawadjusted
FROM
    (store_locations
    LEFT JOIN reserve_transactions ON ((store_locations.ID = location_sales)))
GROUP BY store_locations.Loc_Long

The error is: Error Code: 1292. Truncated incorrect DECIMAL value: ''

I have other queries where the situation is the same, actually. Sometimes with other truncated incorrect types. What am I missing here?

I'd say your database is badly designed if `Revenue` is a `VARCHAR` column, when it obviously should be a numeric column, e.g. `DECIMAL`. — Andreas, Jul 17 '16 at 03:31
Potential duplicate of [mysql error 1292 when using cast in update statement](http://stackoverflow.com/q/21142273/5221149). — Andreas, Jul 17 '16 at 03:35
`Revenue` is a `DECIMAL`, and I couldn't find an answer to my specific problem in that other question, but thanks for that I guess. I just wrapped everything in `IF` to see if an empty string was somehow ending up in the `SUM` from somewhere. — Jacob See, Jul 17 '16 at 03:41
Frankly, why anyone would have that scenario decide on its own the datatypes flying out of a select (for the create) is beyond me. I would create my table explicitly (temporary or not). Then `insert ... select into` it and spare the 3 man hours of pondering this. As if we need to go out of our way to create complexity. — Drew, Jul 17 '16 at 03:54
Can we please stop questioning why I need to do what I'm asking for help with? MySQL is supposed to work this way and I want to make it happen. I need to do it this way because the columns are not known in advance and I'm not writing this query by hand - this is inside of a stored procedure that generates temp tables as caches for complex time-consuming queries and creates them this way using `PREPARE` and `EXECUTE`. — Jacob See, Jul 17 '16 at 04:03
Jacob you are having problems creating a table on the fly. My idea would seem to potentially solve that. Plus I threw in a little humor. Give it a shot (the manual create *and* the grin). Because what you seem to be showing us is a situation where the columns are known in advance. The Happenstance of the first row coming in can sway the datatype for the create. One cannot compare it to "oh it works the other way I am doing it" — Drew, Jul 17 '16 at 07:36

score 0 · Answer 1 · edited May 23 '17 at 12:14

I think that the problem could be data and the logic of selecting only the numeric entries - You could try to define a functions as follows:

CREATE FUNCTION IsNumeric (sIn varchar(1024)) 
RETURNS tinyint
RETURN sIn REGEXP '^(-|\\+){0,1}([0-9]+\\.[0-9]*|[0-9]*\\.[0-9]+|[0-9]+)$';

and apply it in the where clause for the two arguments, eventually trim unnecessary white spaces (as per How do I check to see if a value is an integer in MySQL? )

MySQL query works as normal SELECT but not inside CREATE TABLE AS..?

1 Answers1