OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » census

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

census [message #185250]

Mon, 31 July 2006 14:27

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

I have the following query that is providing expected data.....Diet information and any allergies. However, if one doesn't have any allergy information entered they're not showing up in the results. Can someone point me in the right direction?

Thanks,
Stan

SELECT     
  res1.patient_name Patient_Name,
  res1.patient_id,
  res1.birth_dt,
  res1.dept_id,
  res1.room_id,
  res1.bed_id,
  res1.order_name,
  res1.RESPONSE,
  res1.order_cmt,
  res1.order_seq,
  res1.allergen,
  res1.allergy_type
FROM
(SELECT
     p.last_name|| ',' || p.first_name|| ' ' || p.middle_name     patient_name,
     p.patient_id,
     p.dept_id,
     p.room_id,
     p.bed_id,
     p.birth_dt,
     op.dc_ddt,
   CASE opr.RESPONSE
      when 'Yes' then ''
      when 'No' then ''
      else opr.RESPONSE
   END AS RESPONSE,
     op.sub_seq,
     op.ORDER_CMT,
     op.ORDER_SEQ,
     oi.order_name,
     pfr.result_value ALLERGEN,
     DECODE(LABEL_SEQ,1001,SUBSTR(result_value,1,4)) ALLERGY_TYPE,
     MAX(op.sub_seq)
  OVER 
  (PARTITION BY 
    p.last_name|| ',' || p.first_name|| ' ' || p.middle_name,
    p.patient_id) max_sub_seq
  FROM
     patient p,
     o_pat op,
     o_pat_resp opr,
     o_item oi,
     ccdata.facility f,
     pf_results pfr,
     cpi
     
  WHERE  
         p.pat_seq = op.pat_seq(+)
     AND p.cpi_seq=cpi.cpi_seq
     AND op.ORDER_SEQ=opr.ORDER_SEQ(+)
     AND op.ORDER_ITEM_SEQ=oi.ORDER_ITEM_SEQ
     AND CPI.CPI_SEQ=pfr.CPI_SEQ
     AND pfr.label_seq = 1000
     AND p.facility_id IN (@variable('facilityid'))
     AND oi.PERF_DEPT_ID IN ('NTS')
     AND p.dept_id != 'TRNG'
     AND p.discharge_dt IS NULL
     AND op.frequency_id = 'MEALS'
     AND  ddt.todate(OP.START_DDT)  <  (sysdate + (((.01)*60)/1440))) res1
     
WHERE
     res1.sub_seq = res1.max_sub_seq
        AND res1.dc_ddt IS NULL

UNION

SELECT     
  res2.patient_name Patient_Name,
  res2.patient_id,
  res2.birth_dt,
  res2.dept_id,
  res2.room_id,
  res2.bed_id,
  res2.order_name,
  res2.RESPONSE,
  res2.order_cmt,
  res2.order_seq,
  res2.allergen,
  res2.allergy_type
FROM
(SELECT
     p2.last_name|| ',' || p2.first_name|| ' ' || p2.middle_name     patient_name,
     p2.patient_id,
     p2.dept_id,
     p2.room_id,
     p2.bed_id,
     p2.birth_dt,
     op2.dc_ddt,
   CASE opr2.RESPONSE
      when 'Yes' then ''
      when 'No' then ''
      else opr2.RESPONSE
   END AS RESPONSE,
     op2.sub_seq,
     op2.ORDER_CMT,
     op2.ORDER_SEQ,
     oi2.order_name,
     ddt.todate(opo2.SCHED_DDT) as sdate,
     pfr2.result_value ALLERGEN,
     DECODE(LABEL_SEQ,1001,SUBSTR(result_value,1,4)) ALLERGY_TYPE,
     op2.sub_seq
  FROM
     patient p2,
     o_pat op2,
     o_pat_resp opr2,
     o_pat_occur opo2,
     o_item oi2,
     ccdata.facility f2,
     pf_results pfr2,
     cpi cpi2
     
  WHERE  
          p2.PAT_SEQ=op2.PAT_SEQ(+)
     AND  p2.CPI_SEQ=CPI2.CPI_SEQ 
     AND  op2.ORDER_SEQ=opr2.ORDER_SEQ(+)
     AND  op2.ORDER_ITEM_SEQ=oi2.ORDER_ITEM_SEQ 
     AND  op2.ORDER_SEQ=opo2.ORDER_SEQ 
     AND  CPI2.CPI_SEQ=pfr2.CPI_SEQ
     AND  pfr2.label_seq = 1000
     AND  f2.FACILITY_ID=p2.FACILITY_ID 
     AND  p2.FACILITY_ID  IN (@variable('facilityid'))
     AND  oi2.PERF_DEPT_ID IN ('NTS')
     AND  op2.STATUS IN ('A','F')
     AND  ((DDT.TODATE(op2.DC_DDT) IS NULL)
      OR  (DDT.TODATE(op2.DC_DDT) > sysdate))
     AND  p2.DISCHARGE_DT IS NULL  
     AND  p2.DEPT_ID != 'TRNG'
     AND  trunc(ddt.todate(opo2.SCHED_DDT))  IN  trunc(sysdate)
     AND  ddt.todate(OP2.START_DDT)  <  (sysdate + (((.01)*60)/1440))) res2

Report message to a moderator

Re: census [message #185256 is a reply to message #185250]

Mon, 31 July 2006 15:24

Littlefoot
Messages: 21826
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

"if one doesn't have any allergy information entered they're not showing up in the results."

Well, if one doesn't suffer from any allergy, how can you expect to show information that doesn't exist?

If it was just lapsus calami, perhaps OUTER JOIN could help? You know, in Scott's schema, there's department 50 and noone works in it, but you can write a query which will return all departments (including 50) and employees using outer join.

Report message to a moderator

Re: census [message #185259 is a reply to message #185256]

Mon, 31 July 2006 15:40

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

If somebody doesn't have an allergy I still want them to show up in the results because they will have a diet. This query is basically a diet census that also lists whatever allergies one might have. If one has an allergy they are showing up..... if no allergy they're not showing up even though they need to.

Thanks,
Stan

[Updated on: Mon, 31 July 2006 15:42]

Report message to a moderator

Re: census [message #185262 is a reply to message #185250]

Mon, 31 July 2006 17:02

scottwmackey
Messages: 515
Registered: March 2005

Senior Member

Assuming that pf_results is where the allergy information exists and that is where the record may not exist, you can just do an outer join

SELECT     
  res1.patient_name Patient_Name,
  res1.patient_id,
  res1.birth_dt,
  res1.dept_id,
  res1.room_id,
  res1.bed_id,
  res1.order_name,
  res1.RESPONSE,
  res1.order_cmt,
  res1.order_seq,
  res1.allergen,
  res1.allergy_type
FROM
(SELECT
     p.last_name|| ',' || p.first_name|| ' ' || p.middle_name     patient_name,
     p.patient_id,
     p.dept_id,
     p.room_id,
     p.bed_id,
     p.birth_dt,
     op.dc_ddt,
   CASE opr.RESPONSE
      when 'Yes' then ''
      when 'No' then ''
      else opr.RESPONSE
   END AS RESPONSE,
     op.sub_seq,
     op.ORDER_CMT,
     op.ORDER_SEQ,
     oi.order_name,
     pfr.result_value ALLERGEN,
     DECODE(LABEL_SEQ,1001,SUBSTR(result_value,1,4)) ALLERGY_TYPE,
     MAX(op.sub_seq)
  OVER 
  (PARTITION BY 
    p.last_name|| ',' || p.first_name|| ' ' || p.middle_name,
    p.patient_id) max_sub_seq
  FROM
     patient p,
     o_pat op,
     o_pat_resp opr,
     o_item oi,
     ccdata.facility f,
     pf_results pfr,
     cpi
     
  WHERE  
         p.pat_seq = op.pat_seq(+)
     AND p.cpi_seq=cpi.cpi_seq
     AND op.ORDER_SEQ=opr.ORDER_SEQ(+)
     AND op.ORDER_ITEM_SEQ=oi.ORDER_ITEM_SEQ
     AND CPI.CPI_SEQ=pfr.CPI_SEQ(+)
     AND pfr.label_seq(+) = 1000
     AND p.facility_id IN (@variable('facilityid'))
     AND oi.PERF_DEPT_ID IN ('NTS')
     AND p.dept_id != 'TRNG'
     AND p.discharge_dt IS NULL
     AND op.frequency_id = 'MEALS'
     AND  ddt.todate(OP.START_DDT)  <  (sysdate + (((.01)*60)/1440))) res1
     
WHERE
     res1.sub_seq = res1.max_sub_seq
        AND res1.dc_ddt IS NULL

UNION

SELECT     
  res2.patient_name Patient_Name,
  res2.patient_id,
  res2.birth_dt,
  res2.dept_id,
  res2.room_id,
  res2.bed_id,
  res2.order_name,
  res2.RESPONSE,
  res2.order_cmt,
  res2.order_seq,
  res2.allergen,
  res2.allergy_type
FROM
(SELECT
     p2.last_name|| ',' || p2.first_name|| ' ' || p2.middle_name     patient_name,
     p2.patient_id,
     p2.dept_id,
     p2.room_id,
     p2.bed_id,
     p2.birth_dt,
     op2.dc_ddt,
   CASE opr2.RESPONSE
      when 'Yes' then ''
      when 'No' then ''
      else opr2.RESPONSE
   END AS RESPONSE,
     op2.sub_seq,
     op2.ORDER_CMT,
     op2.ORDER_SEQ,
     oi2.order_name,
     ddt.todate(opo2.SCHED_DDT) as sdate,
     pfr2.result_value ALLERGEN,
     DECODE(LABEL_SEQ,1001,SUBSTR(result_value,1,4)) ALLERGY_TYPE,
     op2.sub_seq
  FROM
     patient p2,
     o_pat op2,
     o_pat_resp opr2,
     o_pat_occur opo2,
     o_item oi2,
     ccdata.facility f2,
     pf_results pfr2,
     cpi cpi2
     
  WHERE  
          p2.PAT_SEQ=op2.PAT_SEQ(+)
     AND  p2.CPI_SEQ=CPI2.CPI_SEQ 
     AND  op2.ORDER_SEQ=opr2.ORDER_SEQ(+)
     AND  op2.ORDER_ITEM_SEQ=oi2.ORDER_ITEM_SEQ 
     AND  op2.ORDER_SEQ=opo2.ORDER_SEQ 
     AND  CPI2.CPI_SEQ=pfr2.CPI_SEQ(+)
     AND  pfr2.label_seq(+) = 1000
     AND  f2.FACILITY_ID=p2.FACILITY_ID 
     AND  p2.FACILITY_ID  IN (@variable('facilityid'))
     AND  oi2.PERF_DEPT_ID IN ('NTS')
     AND  op2.STATUS IN ('A','F')
     AND  ((DDT.TODATE(op2.DC_DDT) IS NULL)
      OR  (DDT.TODATE(op2.DC_DDT) > sysdate))
     AND  p2.DISCHARGE_DT IS NULL  
     AND  p2.DEPT_ID != 'TRNG'
     AND  trunc(ddt.todate(opo2.SCHED_DDT))  IN  trunc(sysdate)
     AND  ddt.todate(OP2.START_DDT)  <  (sysdate + (((.01)*60)/1440))) res2

Report message to a moderator

Re: census [message #185267 is a reply to message #185250]

Mon, 31 July 2006 17:57

staann56
Messages: 136
Registered: May 2006
Location: atlanta

Senior Member

You are right....I had originally tried the joins on the
AND CPI.CPI_SEQ=pfr.CPI_SEQ(+)
and
AND CPI2.CPI_SEQ=pfr2.CPI_SEQ(+)
lines

but not the

AND pfr.label_seq(+) = 1000
and
AND pfr2.label_seq(+) = 1000
lines.

thanks,
Stan

[Updated on: Tue, 01 August 2006 08:32]

Report message to a moderator

Previous Topic:	view user privileges
Next Topic:	How to avoid TEMP tablespace to get full

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Oct 30 18:17:15 CDT 2025