Solved

handling null values in SQL

Posted on 2014-04-01
8
296 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
  • 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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:ScottPletcher
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now