sub query or some tool problem

[shivareddyk]

hi..,

with this query i am getting problem as "Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '='"

select income,bucket, salbucket from
(select income,
(case when income between 0.00 and 2000.00 then 'Bucket1'
when income between 2001.00 and 3000.00 then 'Bucket2'
when income between 3001.00 and 4000.00 then 'Bucket3'
when income between 4001.00 and 6000.00 then 'Bucket4'
when income between 6001.00 and 10000.00 then 'Bucket5'
else 'Bucket6' end) as bucket
from bi02_customer group by bucket) as a,m_caprosel b
where bucket=salbucket;

Note:m_caprosel -->from this table i am geting salbucket as Bucket4.
when i keep where bucket='Bucket4'---->i am getting expected result.

Question?but i didn't understand what is the problem with above my query?

pls correct any one me...

Thanking u in advance........

[dagon]

can you change the db field collation, so they both match? or cast it

[shivareddyk]

hi ,

how we can you change the db field collation?

rgds

[cretaceous]

phpMyAdmin is one option

[Res]

You can use either CONVERT() or CAST() in the query if you do not want to alter the tables. Otherwise you can use an ALTER TABLE command, or do a "INSERT... SELECT ..." with the CONVERT function to rebuild the table as all one char set. I forget the ALTER command exactly, but it's on dev.mysql somewhere. Remember to backup yoru table before you ALTER it 😉

http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html