Solved

Count returning nothing instead of 0

Posted on 2013-01-15
10
760 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 22

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 20

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

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 20

Accepted Solution

by:
flow01 earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

707 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

12 Experts available now in Live!

Get 1:1 Help Now