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
Solved

Count returning nothing instead of 0

Posted on 2013-01-15
10
773 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
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.

 

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 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.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

840 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