handling null values in SQL

In Line No 15 include one more condition:

OR "HRxs"."MOP2" IS NULL

Select allOpen in new window

You can do this:

AND ("HRxs"."MOP" IN('WOFF') OR ("HRxs"."MOP2" IS NULL or "HRxs"."MOP2" IN('WOFF')))

It should return the correct records.

JMILLER,
YOU MEAN LIKE THIS

 SELECT "Facilities"."FacID", "Facilities"."FacName", 
 (Case
	 When hrxs.Reversed = 0 then RXNO 
	 else
	 Case 
	 when hrxs.transtype in ('P','B','Q','U') then RXNO 
	else 
	case 
	when hrxs.CreditAmt is Not Null then 0 else -RxNo end end end) as Rxno, "HRxs"."DispenseDt", "HRxs"."NDC", "HRxs"."DrugLabelName", 
 "HRxs"."Qty", "HRxs"."MOP", "Patients"."PatLName"+', '+ "Patients"."PatFName", "HRxs"."Payor1PaidAmt", "HRxs"."MOP2", "HRxs"."PatientPayAmt"
 FROM   ("FwReports"."dbo"."HRxs" "HRxs" 
 LEFT OUTER JOIN "FwReports"."dbo"."Facilities" "Facilities" ON "HRxs"."FacID"="Facilities"."FacID") 
 LEFT OUTER JOIN "FwReports"."dbo"."Patients" "Patients" ON ("HRxs"."FacID"="Patients"."FacID") AND ("HRxs"."PatID"="Patients"."PatID")
 WHERE  HRxs.facid in(select slice1 from #TempIDTable2) and
	HRxs.facid in(select slice1 from #TempIDTable2) 
	AND ("HRxs"."MOP" =@mop) OR  ("HRxs"."MOP2" IS NULL or "HRxs"."MOP2" =@mop)
	AND DispenseDt between @MStart and @MEnd  AND "HRxs"."PatientPayAmt"<>0 and  coalesce(UDRx, 0) = 0 and Reversed =0

Select allOpen in new window

Yes, because that should grab the record when the MOP2 value is NULL or equal to the @mop parameter. Grouping them in the parenthesis keeps those two together.

jmiller, I tried it and the problem is it is picking every row that has null on MOP2 even though MOP is not equal to 'WOFF' .

Your original conditions should work fine for that part.

But the other columns in the WHERE could be a problem.

I can't tell which table those columns are from.  Please prefix the other columns in the WHERE with the appropriate table/table alias:

WHERE
DispenseDt between @MStart and and @MEnd  AND
"HRxs"."PatientPayAmt"<>0 and  
coalesce(UDRx, 0) = 0 and
Reversed =0

Thanks.

I just noticed a couple of things.

This case expression will do exactly the same job as one  being used

CASE
 WHEN hrxs.Reversed = 0 THEN RXNO
 WHEN hrxs.transtype IN ('P', 'B', 'Q', 'U') THEN RXNO
 WHEN hrxs.CreditAmt IS NOT NULL THEN 0
 ELSE -RxNo
END

If the first when condition is met, that is done and the rest ignored.
if the first is not met, but the second is met, that is done and the rest ignored.
If the first 2 are not met, but the third is met, that is done and the rest ignored
If none of the when conditions are met, then the else is done.

It isn't necessary to nest the conditions like this CASE ... CASE ... CASE .... END END END

---
I see you are using "between" for your date range, this can be a problem which I attempt to explain in "Beware of Between"

Cheers,
Paul