question

john123 avatar image
john123 asked

how to use cast,in mysql when union is used

how to apply cast for 'meterscount', when applying cast it is showing the error illegal mix of collations in 'union'. (SELECT StartNo,Category,Label,User FROM ShowData WHERE SelectList=5) UNION (SELECT '','','',CONCAT('Meter Subtotal: ',MetersCount));
mysqlunioncollation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
eghetto avatar image
eghetto answered
(SELECT StartNo ,Category ,Label ,User FROM ShowData WHERE SelectList = 5) UNION (SELECT CAST('' AS ?) -- replace ? by data type of 'StartNo'! ,CAST('' AS ?) -- replace ? by data type of 'Category'! ,CAST('' AS ?) -- replace ? by data type of 'Label'! ,CAST(CONCAT('Meter Subtotal: ', MetersCount)) AS ?); -- replace ? by data type of 'User'!
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
And, if the collation doesn't match between the column in your table and the collation of the value coming out of the cast, you can cast using a specific character set - http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.