Home » SQL & PL/SQL » SQL & PL/SQL » not exists
not exists [message #184221] Tue, 25 July 2006 14:57 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'm trying to query on those that have never had a diet ordered. The subquery (after the NOT EXISTS) returns a diet census so I added the few lines above it thinking it would return anyone who didn't have a diet ordered....but it's not working.

Any thoughts...


SELECT     
  c.client_id,
FROM
  client c
WHERE 
 NOT EXISTS

(SELECT     
  sub1.client_name,
  sub1.client_id,
  sub1.order_name,
  sub1.RESPONSE,
  sub1.order_cmt,
  sub1.order_seq
FROM
(SELECT
     c.last_name|| ',' || c.first_name|| ' ' || c.middle_name     client_name,
     c.client_id,
   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,
     MAX(op.sub_seq)
  OVER 
  (PARTITION BY 
    c.last_name|| ',' || c.first_name|| ' ' || c.middle_name,
    c.client_id) max_sub_seq
  FROM
     client c,
     o_part op,
     o_part_resp opr,
     o_item oi,
     ccdata.facility f,
     cpi     
  WHERE  
         c.pat_seq = op.pat_seq (+)
     AND op.ORDER_SEQ=opr.ORDER_SEQ(+)
     AND c.cpi_seq=cpi.cpi_seq
     AND op.ORDER_ITEM_SEQ=oi.ORDER_ITEM_SEQ
     AND c.facility_id IN ('P')
     AND oi.PERF_DEPT_ID IN ('NHS')
     AND c.dept_id != 'TRNG'
     AND c.discharge_dt IS NULL
     AND op.frequency_id = 'MEALS') sub1
WHERE
     sub1.sub_seq = sub1.max_sub_seq
        AND sub1.dc_ddt IS NULL)



thanks,
stan
Re: not exists [message #184241 is a reply to message #184221] Tue, 25 July 2006 19:12 Go to previous message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I think you might want to use NOT IN
SELECT c.client_id
      ,
FROM client c
WHERE client_id NOT IN
      (SELECT sub1.client_id
       FROM (SELECT c.last_name || ',' || c.first_name || ' ' || c.middle_name client_name
                   ,c.client_id
                   ,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
                   ,MAX(op.sub_seq) over(PARTITION BY c.last_name || ',' || c.first_name || ' ' || c.middle_name, c.client_id) max_sub_seq
             FROM client c
                 ,o_part op
                 ,o_part_resp opr
                 ,o_item oi
                 ,ccdata.facility f
                 ,cpi
             WHERE c.pat_seq = op.pat_seq(+)
             AND op.order_seq = opr.order_seq(+)
             AND c.cpi_seq = cpi.cpi_seq
             AND op.order_item_seq = oi.order_item_seq
             AND c.facility_id IN ('P')
             AND oi.perf_dept_id IN ('NHS')
             AND c.dept_id != 'TRNG'
             AND c.discharge_dt IS NULL
             AND op.frequency_id = 'MEALS') sub1
       WHERE sub1.sub_seq = sub1.max_sub_seq
       AND sub1.dc_ddt IS NULL)
Previous Topic: Joins
Next Topic: Huge Update
Goto Forum:
  


Current Time: Sat Dec 03 09:56:07 CST 2016

Total time taken to generate the page: 0.06593 seconds