handling null values in SQL

Hello All,
I have the below query:
 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" IN('WOFF') OR "HRxs"."MOP2" IN('WOFF'))
		AND DispenseDt between @MStart and @MEnd  AND "HRxs"."PatientPayAmt"<>0 and  coalesce(UDRx, 0) = 0 and Reversed =0

Open in new window


The above query is not returning the below record as it has a null value on the mop2, How can I handle it.I need my query to return such data too when there is a null value on the mop2 though mop='WOF'


Please help.

MOP      MOP2      PatientPayAmt      TransType      reversed      UDRx
WOFF      NULL      0.00      Q      0      0
Star79Asked:
Who is Participating?
 
PortletPaulConnect With a Mentor Commented:
try this perhaps:
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 AS HRxs
      INNER JOIN (
            SELECT
                  slice1
            FROM #TempIDTable2
      ) AS TMP2
            ON HRxs.facid = TMP2.slice1
      LEFT OUTER JOIN FwReports.dbo.Facilities AS Facilities
            ON HRxs.FacID = Facilities.FacID
      LEFT OUTER JOIN FwReports.dbo.Patients AS Patients
            ON HRxs.FacID = Patients.FacID
                  AND HRxs.PatID = Patients.PatID
WHERE (
        HRxs.MOP IN ('WOFF')
        AND (HRxs.MOP2 IN ('WOFF') OR HRxs.MOP2 IS NULL)
      )
      AND DispenseDt BETWEEN @MStart AND @MEnd
      AND HRxs.PatientPayAmt <> 0
      AND COALESCE(UDRx, 0) = 0
      AND Reversed = 0

Open in new window

note I have changed your joins!
You seem to be requesting this "HRxs.facid in(select slice1 from #TempIDTable2" twice and I didn't see why you would do that - plus I'd much rather use a join for this myself.

I've also removed unnecessary parentheses from the joins so I could follow them. You're free to add them back again, but I wouldn't.

>>"the problem is it is picking every row that has null on MOP2 even though MOP is not equal to 'WOFF' "

That would be due to not "pairing" the conditions I think which I suggest should be as follows"

WHERE (
        HRxs.MOP IN ('WOFF')
        AND (HRxs.MOP2 IN ('WOFF') OR HRxs.MOP2 IS NULL)
      )
0
 
Easwaran ParamasivamCommented:
In Line No 15 include one more condition:

OR "HRxs"."MOP2" IS NULL

Open in new window

0
 
Jerry MillerCommented:
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.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Star79Author Commented:
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

Open in new window

0
 
Jerry MillerCommented:
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.
0
 
Star79Author Commented:
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' .
0
 
Scott PletcherSenior DBACommented:
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
0
 
PortletPaulCommented:
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
0
All Courses

From novice to tech pro — start learning today.