Home » SQL & PL/SQL » SQL & PL/SQL » Outer Join help (Oracle 9i)
Outer Join help [message #280661] Wed, 14 November 2007 07:12 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to outer join the results of two queries but am not getting the desired result set. If I run the DEMO query I get 25 records and if I run the ADIR query I get 350. I would expect the combined outer joined result set to equal 350 but it equals 25.

What am I missing? Any help would be greatly appreciated.

Stan

SELECT
   DEMO.*,
   ADIR.*
FROM


(SELECT /*+ ORDERED */
   CASE
      WHEN p.facility_id = 'A' THEN 'MMC'
      WHEN p.facility_id = 'B' THEN 'FSRMC'
      WHEN p.facility_id = 'C' THEN 'FLMC'
      WHEN p.facility_id = 'D' THEN 'PWMC'
      WHEN p.facility_id = 'E' THEN 'FSS'
   END FAC,
   INITCAP(p.last_name || ', ' || p.first_name || ' ' || p.middle_name) Patient_Name,
   p.patient_id,
   p.pat_seq,
   p.cpi_seq,
   TO_CHAR(p.admit_dt,'MM/DD/YYYY HH24:MI') Admit_Date,
   p.dept_id,
   oi.order_name,
   DDT.TOCHAR(op.start_ddt,'MM/DD/YYYY HH24:MI') Order_Start_Date
FROM
   p,
   op,
   oi
WHERE
   p.pat_seq = op.pat_seq AND
   op.order_item_seq = oi.order_item_seq  AND
   DDT.TOCHAR(OP.START_DDT,'MM/DD/YYYY HH24:MI') BETWEEN TO_CHAR(ADMIT_DT,'MM/DD/YYYY HH24:MI') AND TO_CHAR(((ADMIT_DT)+1),'MM/DD/YYYY HH24:MI') AND
   p.facility_id IN ('A','B','C','D','E') AND
   oi.order_item_seq IN (2141,2132,2343,2135,2141) AND
   p.pat_seq IN 
      (SELECT /*+ PUSH_SUBQ */
         p.pat_seq
      FROM
         p
      WHERE
         p.admit_dt BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') 
AND TRUNC(ADD_MONTHS(SYSDATE,0),'MM')-(1/86400))) DEMO,
                
         
(SELECT
   RES1.pat_seq,
   RES1.cpi_seq,
   MIN(RES1.ADIR) || ' - ' || DDT.TOCHAR(perform_ddt,'MM/DD/YYYY HH24:MI') AS ADIR,
   RES1.admit_dt
FROM
   (SELECT /*+ ORDERED */
      p.pat_seq,
      p.cpi_seq,
      DECODE(pfo.label_seq,89478,pfr.result_value) ADIR,
      perform_ddt,
      TO_CHAR(p.admit_dt,'MM/DD/YYYY HH24:MI') ADMIT_DT
   FROM
      p,
      pfr,
      pfo
   WHERE
      pfr.cpi_seq = p.cpi_seq  AND
      pfo.label_seq = pfr.label_seq  AND
      p.facility_id IN ('A','B','C','D','E') AND
      pfr.perform_ddt BETWEEN DDT.FROMDATE(p.admit_dt) AND DDT.FROMDATE(p.admit_dt+1) AND
      pfo.label_seq = 894 AND
      p.pat_seq IN
         (SELECT /*+ PUSH_SUBQ */
            p.pat_seq
         FROM
            patient p
         WHERE
            p.admit_dt BETWEEN TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') AND TRUNC(ADD_MONTHS(SYSDATE,0),'MM')-(1/86400))) RES1
WHERE
   RES1.ADIR = 'Yes'
GROUP BY 
   RES1.pat_seq,
   RES1.cpi_seq,
   perform_ddt,
   RES1.admit_dt) ADIR


WHERE
   DEMO.pat_seq = ADIR.pat_seq(+) 
Re: Outer Join help [message #280664 is a reply to message #280661] Wed, 14 November 2007 07:24 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Could it be that you're mixing up the place of the + sign in your join condition?
Re: Outer Join help [message #280666 is a reply to message #280661] Wed, 14 November 2007 07:37 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It seems you expect results of RIGHT join of DEMO and ADIR, however you are making LEFT join.
If so, simply move the (+) operator to DEMO.pat_seq
Read about the difference in OUTER JOIN chapter of SQL Reference.

WHERE DDT.TOCHAR(OP.START_DDT,'MM/DD/YYYY HH24:MI')
  BETWEEN TO_CHAR(ADMIT_DT,'MM/DD/YYYY HH24:MI')
  AND TO_CHAR(((ADMIT_DT)+1),'MM/DD/YYYY HH24:MI')
Do you have any reason for comparing strings instead of dates?
Are you aware that '01/01/2008 00:00' < '12/31/2007 23:59'?
For ADMIT_DT = to_date( '12/31/2007 12:00', 'MM/DD/YYYY HH24:MI' ) you will get
DDT.TOCHAR(OP.START_DDT,'MM/DD/YYYY HH24:MI') BETWEEN '12/31/2007 12:00' AND '01/01/2008 12:00'
, which is always false.
Re: Outer Join help [message #280999 is a reply to message #280661] Thu, 15 November 2007 06:30 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Flyboy,

That did it and you're correct about the date. I just haven't converted it yet.

Thanks for your help,
Stan
Previous Topic: begginers to sql
Next Topic: Triggers failling.......
Goto Forum:
  


Current Time: Wed Dec 07 14:28:05 CST 2016

Total time taken to generate the page: 0.08814 seconds