Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Count returning nothing instead of 0

Posted on 2013-01-15
10
Medium Priority
?
799 Views
Last Modified: 2013-01-15
We have a large database schema with patient encounter information. Im trying to
write something that will count how many previous encounters a patient is had,
with respect to a specific encounter.

Some facts:

Each patient has a unique patient ID (PAT_ID)
Each encounter has a unique contact serial number (PAT_ENC_CSN_ID)
Each encounter has a contact date.

I wrote:

SELECT
PAT_ENC_NOW.PAT_ENC_CSN_ID,
count(PAST_ENC.PAT_ENC_CSN_ID)as countpast
FROM
CLARITY.PAT_ENC PAT_ENC_NOW,
CLARITY.PAT_ENC PAST_ENC
WHERE
(PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID)
AND
(PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE)
AND
(PAT_ENC_NOW.PAT_ENC_CSN_ID = 1234567)
GROUP BY PAT_ENC_NOW.PAT_ID,PAT_ENC_NOW.PAT_ENC_CSN_ID


I know (from a manual count) that encounter 1234567 has 12 previous encounters;
when I run this in SQL Developer, I get the expected results:



PAT_ENC_CSN_ID COUNTPAST
-------------- ---------
1234567        12



However, when I run the same query, with a PAT_ENC_CSN_ID = 67676767,
that I know (again, from a manual count) has no previous encounters, I get....well...nothing:




PAT_ENC_CSN_ID COUNTPAST
-------------- ---------



Here's what I'm hoping to see:




PAT_ENC_CSN_ID COUNTPAST
-------------- ---------
67676767       0


How do I get it to return 0 instead of nothing?
0
Comment
Question by:diannagibbs
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38780146
Since there's no records in the contact table, there's nothing to group on.

Assuming that the patient record exists in the patient table something like this will probably work (and my limited testing confirms it).

select b.patient_id, count(a.patient_id)
from patients b 
left outer join encounters a on a.patient_id = b.patient_id
group by b.patient_id

Open in new window


You can place a where b.patient_id = <parameter> in there before the group by to limit to one patient.
0
 
LVL 21

Expert Comment

by:flow01
ID: 38780197
but because the outer join delivers NULL values for the non-existing row  

with pat_enc
as
(
select 2 pat_id,  67676767 pat_enc_csn_id, sysdate - 3 contact_date from dual union
select 2 pat_id,  888888 pat_enc_csn_id, sysdate - 2 contact_date from dual union
select 2 pat_id,  1234567 pat_enc_csn_id, sysdate - 1 contact_date from dual
)
SELECT
PAT_ENC_NOW.PAT_ENC_CSN_ID,
count(PAST_ENC.PAT_ENC_CSN_ID)as countpast
from  
PAT_ENC PAT_ENC_NOW
left outer join PAT_ENC PAST_ENC
on
(PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID)
and
(PAST_ENC.CONTACT_DATE is null or PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE)
where
--(PAT_ENC_NOW.PAT_ENC_CSN_ID = 1234567)
(PAT_ENC_NOW.PAT_ENC_CSN_ID = 67676767)
GROUP BY PAT_ENC_NOW.PAT_ID,PAT_ENC_NOW.PAT_ENC_CSN_ID
/
0
 
LVL 35

Expert Comment

by:YZlat
ID: 38780204
try something like this:

SELECT 
PAT_ENC_NOW.PAT_ENC_CSN_ID, 
NVL(count(PAST_ENC.PAT_ENC_CSN_ID), 0) as countpast 
FROM 
CLARITY.PAT_ENC PAT_ENC_NOW, 
CLARITY.PAT_ENC PAST_ENC 
WHERE 
(PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID) 
AND 
(PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE) 
AND 
(PAT_ENC_NOW.PAT_ENC_CSN_ID = 67676767) 
GROUP BY PAT_ENC_NOW.PAT_ID,PAT_ENC_NOW.PAT_ENC_CSN_ID 

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:diannagibbs
ID: 38780220
NVL still gives me "no rows"
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38780230
Try this:


SELECT
NVL(PAT_ENC_NOW.PAT_ENC_CSN_ID,67676767) PAT_ENC_CSN_ID,
SUM(DECODE(PAST_ENC.PAT_ENC_CSN_ID,NULL,0,1))as countpast
FROM
CLARITY.PAT_ENC PAT_ENC_NOW,
CLARITY.PAT_ENC PAST_ENC,
(SELECT 67676767 PAT_ENC_CSN_ID FROM DUAL) D
WHERE
(PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID(+))
AND
(PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE(+))
AND
(PAT_ENC_NOW.PAT_ENC_CSN_ID(+) = D.PAT_ENC_CSN_ID)

GROUP BY NVL(PAT_ENC_NOW.PAT_ENC_CSN_ID,67676767)
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38780256
Actually, in the limited testing I did, the count returned a 0 - I was expecting an issue with the null, but it returned 0
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38780281
This SQL only needs the PAT_ENC_CSN_ID to be entered once:

SELECT
NVL(PAT_ENC_NOW.PAT_ENC_CSN_ID,D.PAT_ENC_CSN_ID) PAT_ENC_CSN_ID,
SUM(DECODE(PAST_ENC.PAT_ENC_CSN_ID,NULL,0,1))as countpast
FROM
CLARITY.PAT_ENC PAT_ENC_NOW,
CLARITY.PAT_ENC PAST_ENC,
(SELECT 67676767 PAT_ENC_CSN_ID FROM DUAL) D
WHERE
(PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID(+))
AND
(PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE(+))
AND
(PAT_ENC_NOW.PAT_ENC_CSN_ID(+) = D.PAT_ENC_CSN_ID)

GROUP BY NVL(PAT_ENC_NOW.PAT_ENC_CSN_ID,NVL(PAT_ENC_NOW.PAT_ENC_CSN_ID,D.PAT_ENC_CSN_ID))
0
 
LVL 21

Accepted Solution

by:
flow01 earned 2000 total points
ID: 38780365
and in outer join (+) syntax

SELECT
PAT_ENC_NOW.PAT_ENC_CSN_ID,
count(PAST_ENC.PAT_ENC_CSN_ID)as countpast
from
PAT_ENC PAT_ENC_NOW
,PAT_ENC PAST_ENC
where
PAT_ENC_NOW.PAT_ID = PAST_ENC.PAT_ID(+)
and
PAT_ENC_NOW.CONTACT_DATE > PAST_ENC.CONTACT_DATE(+)
and
--(PAT_ENC_NOW.PAT_ENC_CSN_ID = 1234567)
PAT_ENC_NOW.PAT_ENC_CSN_ID = 67676767
GROUP BY PAT_ENC_NOW.PAT_ID,PAT_ENC_NOW.PAT_ENC_CSN_ID
/
0
 

Author Comment

by:diannagibbs
ID: 38780404
I believe, flow01, this is exactly what we're looking for.  Let me test it.  Thanks!!!!
0
 

Author Closing Comment

by:diannagibbs
ID: 38780469
Exactly what we're looking for - thanks!!!!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

810 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