CASE WHEN IS NULL

Question

I want to put the PlaceTypeId = 18 in the column PlaceType.PlaceTypeId if is it null. I tried with this case null but it don't work. Am I doing something wrong?

SELECT DoctorsAddress.AddressId, DoctorsAddress.Address, PostalCode.Locality, 
  (CASE WHEN (PlaceType.PlaceTypeId IS NULL) THEN 18 ELSE    
   DoctorsAddress.PlaceTypeId END) AS TipoLocal, 
  (CASE WHEN (placeofvisitquery.placeofvisit IS NULL) THEN '' ELSE
   DoctorEnterpriseDetails.Schedule END) AS Schedule, 
  PostalCode.PostalCode, DoctorsAddress.DoctorId, 
  (CASE WHEN (placeofvisitquery.placeofvisit IS NULL) THEN '' ELSE 'X' END) AS teste
FROM  DoctorsAddress 
 INNER JOIN PlaceType ON DoctorsAddress.PlaceTypeId = PlaceType.PlaceTypeId
 INNER JOIN PostalCode ON DoctorsAddress.PostalCode = PostalCode.PostalCodeId 
 INNER JOIN DoctorEnterpriseDetails ON DoctorsAddress.DoctorId = DoctorEnterpriseDetails.DoctorId 
 LEFT OUTER JOIN DoctorRepresentative ON
   DoctorEnterpriseDetails.EnterpriseId = DoctorRepresentative.EnterpriseId AND 
   DoctorsAddress.DoctorId = DoctorRepresentative.DoctorId AND                       
   DoctorsAddress.AddressId = DoctorRepresentative.placeofvisit 
 LEFT OUTER JOIN placeofvisitquery ON 
   DoctorsAddress.DoctorId = placeofvisitquery.DoctorId AND
   DoctorsAddress.AddressId = placeofvisitquery.placeofvisit
WHERE (DoctorsAddress.DoctorId = @param2) AND (DoctorEnterpriseDetails.EnterpriseId = 26)

instead of case use coalesce(field value, value when null, value when null etc) example `coalesce(placeofvisitquery.placeofvisit, 18)` however what you have seems like it should work ( at start of case is unnecessary, but it shouldn't harm your results.. are you getting an error or is it just not returning expected results? — xQbert, Aug 31 '15 at 14:44
@xQbert It just not returning the result , returns the entire row empty — Mara Pimentel, Aug 31 '15 at 14:48
Could it be that PlaceType will never be null, because you used `DoctorsAddress INNER JOIN PlaceType ON DoctorsAddress.PlaceTypeId = PlaceType.PlaceTypeId`; if you replace that with a `LEFT OUTER JOIN` you should get null values for PlaceTypeId (that will be evaluated in your `CASE...`) — DrCopyPaste, Aug 31 '15 at 14:48

score 3 · Accepted Answer · answered Aug 31 '15 at 15:01

I think your problem is that you used an INNER JOIN between DoctorsAddress.PlaceTypeId and PlaceType.PlaceTypeId, so that will join every row with a value (and that excludes nulls) in DoctorsAddress with every corresponding row from PlaceType.

So you never get any row that actually has PlaceType.PlaceTypeId IS NULL. You can have these though if you replace

INNER JOIN PlaceType ON DoctorsAddress.PlaceTypeId = PlaceType.PlaceTypeId

with

LEFT OUTER JOIN PlaceType ON DoctorsAddress.PlaceTypeId = PlaceType.PlaceTypeId

Then (in case DoctorsAddress.PlaceTypeId is null) you will have the selected row from DoctorsAddress, but no corresponding row in PlaceType will be found, because DoctorsAddress.PlaceTypeId IS NULL and because you cannot compare against NULL, hence every member of PlaceType will also be NULL.

CASE WHEN IS NULL

1 Answers1