Why do I get a syntax error when using CAST in MySQL?

Question

I am using MySQL workbench v5.2.44 CE. I am running it against a local MySQL 5.5 install.

I am trying to use the CAST function, but keep getting the following error:

syntax error, unexpected INT_SYM

It doesn't matter what the source and target date types are. The only time it doesn't give me an error is when the target datatype is DECIMAL. Here is an example:

SELECT CAST(IFNULL(comboCount, 1) * COUNT(partID) AS INT) INTO comboCount
FROM productOption

I have tried everything, but nothing seems to work.

Answer 1

Try to do the math outside:

SELECT CAST(IFNULL(comboCount, 1) AS INT) * COUNT(partID) INTO comboCount
FROM productOption

If that doesn't work, try to CAST as UNSIGNED; not INT.