Case statement failing

[jack van Zanen]

Hi All,

I have above statement that works fine on MYSQL 3.23.58 on W2K but on Linux/3.23.53 it shows up NULL values for all fields except for the field value of the first row in custon_field_string_table (in my case 'CS number') so it evaluates all mantis_custom_field_table.name as CS number and fills all my columns with NULL'S

Is there a limit on the case statements in earlier versions of MYSQL
(BTW I can not upgrade)


Thx


Jack


************************************************
create table cross_table as SELECT case
when mantis_custom_field_table.name= 'Accepted by RM'
then mantis_custom_field_string_table.value
else null
end 'AcceptedbyRM',
case
when mantis_custom_field_table.name= 'CS number'
then mantis_custom_field_string_table.value
else null
end 'CSnumber',
case
when mantis_custom_field_table.name= 'Development environment'
then mantis_custom_field_string_table.value
else null
end 'Developmentenvironment',
case
when mantis_custom_field_table.name= 'FMM related'
then mantis_custom_field_string_table.value
else null
end 'FMMrelated',
case
when mantis_custom_field_table.name= 'Main System'
then mantis_custom_field_string_table.value
else null
end 'MainSystem',
case
when mantis_custom_field_table.name= 'Missing RM info'
then mantis_custom_field_string_table.value
else null
end 'MissingRMinfo',
case
when mantis_custom_field_table.name= 'Other applications involved'
then mantis_custom_field_string_table.value
else null
end 'Otherapplicationsinvolved',
case
when mantis_custom_field_table.name= 'Planned date Release'
then mantis_custom_field_string_table.value
else null
end 'PlanneddateRelease',
case
when mantis_custom_field_table.name= 'Proces'
then mantis_custom_field_string_table.value
else null
end 'Proces',
case
when mantis_custom_field_table.name= 'Projectleader'
then mantis_custom_field_string_table.value
else null
end 'Projectleader',
case
when mantis_custom_field_table.name= 'Signed off CR form attached?'
then mantis_custom_field_string_table.value
else null
end 'SignedoffCRformattached',
case
when mantis_custom_field_table.name= 'Submitted By'
then mantis_custom_field_string_table.value
else null
end 'SubmittedBy',
case
when mantis_custom_field_table.name= 'Type'
then mantis_custom_field_string_table.value
else null
end 'Type',
mantis_custom_field_string_table.bug_id
FROM
mantis_custom_field_table
INNER JOIN mantis_custom_field_string_table ON (mantis_custom_field_table.id = mantis_custom_field_string_table.field_id)