At work I automate reports that usually pull a really large chunk of data from several tables in our loan origination software. Today I came across a report with a blank value where I was concatenating 2 fields together:
CASE SubProp.PropertyType WHEN 0 THEN 'SINGLE FAMILY' WHEN 1 THEN 'SINGLE FAMILY' WHEN 2 THEN 'SINGLE FAMILY' WHEN 3 THEN 'CONDO' WHEN 4 THEN 'HIGH RISE CONDO' WHEN 5 THEN 'DETACHED CONDO' WHEN 6 THEN 'PUD' WHEN 7 THEN 'COOPERATIVE' WHEN 8 THEN 'MANUFACTURED' WHEN 9 THEN 'MANUFACTURED CONDO/PUD/CO-OP' WHEN 10 THEN 'MANUFACTURED SINGLEWIDE' WHEN 11 THEN 'MANUFACTURED SINGLEWIDE' WHEN 12 THEN 'SINGLE FAMILY' ELSE 'SINGLE FAMILY' END +' '+ CASE SubProp.NoUnits WHEN 1 THEN '1 UNIT' WHEN 2 THEN '2 UNITS' WHEN 3 THEN '3 UNITS' WHEN 4 THEN '4 UNITS' WHEN 5 THEN '5 UNITS' WHEN 6 THEN '6 UNITS' END AS 'PROPERTY_TYPE'
So while the first case statement would always default to SOME value, the second case statement would not. I had assumed if it came through as blank it would just add a blank to the end of the value stored in that column. WRONG. It actually replaced the entire value with an empty string so the cell in the spreadsheet was blank. I’m still not really sure why that happened, but the fix was to add this line to the second case statement: ELSE ”. Now the cell contains the first case statement’s value, and nothing for the second. Just weird.