Solved

handling null values in SQL

Posted on 2014-04-01
8
308 Views
Last Modified: 2014-04-14
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
0
Comment
Question by:Star79
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 39970352
In Line No 15 include one more condition:

OR "HRxs"."MOP2" IS NULL

Open in new window

0
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39970358
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
 

Author Comment

by:Star79
ID: 39970483
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Expert Comment

by:Jerry Miller
ID: 39970490
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
 

Author Comment

by:Star79
ID: 39970577
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39970691
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39970973
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40000518
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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question