CASE Within a CASE to replace a null value

[mike55]

I have the following SQL statement:

 

SELECT tbMeasurement.MonitoredEntityCode, CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_1' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_1' 
                       ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_2' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_2'
                           ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_3' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_3' 
                               ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_4' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_4' 
                                   ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_5' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_5' 
                                       ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_6' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_6' 
                                           ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_7' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_7' 
                                               ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_8' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_8' 
                                                   ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'EMISSION_POINT_9' THEN tbMeasurement.NumericResult END AS 'EMISSION_POINT_9' 
                                                    ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'FUGITIVE_TOTAL' THEN tbMeasurement.NumericResult END AS 'FUGITIVE_TOTAL' 
                                                        ,CASE WHEN tbMeasurement.MonitoredLocationCode = 'ACCIDENTAL_TOTAL' THEN tbMeasurement.NumericResult END AS 'ACCIDENTAL TOTAL',
                        tbMeasurement.SampleID, tbMeasurement.ParameterCode, tbParameter.ParameterDescription, 
                            tbMeasurementExtraData.M_C_E, tbMeasurementExtraData.MethodCode, 
                               tbMeasurementExtraData.Description 
                   FROM (tbMeasurement INNER JOIN 
                 tbParameter ON tbMeasurement.ParameterCode = tbParameter.ParameterCode) INNER JOIN 
                     tbMeasurementExtraData ON (tbMeasurement.ParameterCode = tbMeasurementExtraData.ParameterCode) 
                      AND (tbMeasurement.SampleID = tbMeasurementExtraData.SampleID) 
                          AND (tbMeasurement.MonitoredLocationCode = tbMeasurementExtraData.MonitoredLocationCode) 
                              AND (tbMeasurement.MonitoredEntityCode = tbMeasurementExtraData.MonitoredEntityCode) 

 

As you can see I as using a Case statement to create a new column for each type of emission point found and for Fugitive and Accidental Totals. What I am also trying to do is to replace the null values returned for Fugitive and Accidental Totals with a 0. I have tried using an inner case statement but that doesn't seem to work, any suggestions? I have also tried the replace statement, no effect.

 

Mike55.

[PlausiblyDamp]

Could you not just use the IsNull function to handle changing the nulls to 0?

[Diesel]

or the Coalesce function.

 

http://msdn.microsoft.com/en-us/library/ms190349.aspx