Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

handling null values in SQL

Posted on 2014-04-01
8
Medium Priority
?
316 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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 70

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Industry Leaders: 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!

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

636 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