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 doesnt 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