Count returning nothing instead of 0

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?
diannagibbsAsked:
Who is Participating?
 
flow01Commented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
flow01Commented:
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
YZlatCommented:
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
 
diannagibbsAuthor Commented:
NVL still gives me "no rows"
0
 
paquicubaCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
paquicubaCommented:
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
 
diannagibbsAuthor Commented:
I believe, flow01, this is exactly what we're looking for.  Let me test it.  Thanks!!!!
0
 
diannagibbsAuthor Commented:
Exactly what we're looking for - thanks!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.