Home » SQL & PL/SQL » SQL & PL/SQL » Excluding records in result set
Excluding records in result set [message #291677] Sat, 05 January 2008 17:44 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I'll try this again - Moderator, please delete my post from 01/03/2008. I've since added the CREATE TABLE and INSERT INTO statements.



I'm trying to exclude records for the same pseq from the result set where:

(ORD = 7 AND perform_ddt = mcd) for a record AND (ORD = MINORD AND perform_ddt != mcd) for a different record within the same pseq.

For example, for the result set:


p_name     pid    pseq  fid branch        lseq ord minord perform_ddt         mcd

smith, j   1234   345   F   Gainesville   845   1   1     12/10/2007 13:00   12/12/2007 15:43
smith, j   1234   345   F   Gainesville   845   1   1     12/10/2007 14:43   12/12/2007 15:43
smith, j   1234   345   F   Home Care     934   7   1     12/12/2007 15:43   12/12/2007 15:43



I wouldn't want them to show up in the result set at all.

Additionally,
If the ORD = 7 and the perform_ddt = mcd for a record within a pseq AND ORD IN (1,2,3,4,5) and the perform_ddt = mcd for a record within the same pseq then I only want to report the record where ORD IN (1,2,3,4,5)....and not the record where ORD = 7.

How do I go about this? I've even tried to use a CASE STATEMENT
with DENSE_RANK like below in the outer query:

   CASE
      WHEN (MIN(ORD) KEEP (DENSE_RANK FIRST ORDER BY ORD) OVER (PARTITION BY pat_seq,ORD) IN (1,2,3,4,5) AND PERFORM_DDT != MCD  AND
           MAX(ORD) KEEP (DENSE_RANK LAST ORDER BY ORD) OVER (PARTITION BY pat_seq,ord) IN (6)  AND PERFORM_DDT = MCD)
      THEN (CASE WHEN ORD = MINORD THEN result_value END)
   END res2,




Any help would be greatly appreciated.

Thanks,
Stan



CREATE TABLE PR (
   PSEQ		NUMBER(3),
   RV		CHAR(30),
   PERFORM_DDT	DATE,
   LSEQ		NUMBER(3), );

CREATE TABLE P (
   LAST_NAME	CHAR(10),
   FIRST_NAME	CHAR(10),
   MIDDLE_NAME	CHAR(10),
   PID		NUMBER(4),
   PSEQ		NUMBER(3),
   FID		CHAR(1), );


   
INSERT INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ) 
VALUES (345, 'Gainesville', TO_DATE('12/10/2007 13:00', 'MM/DD/YYYY HH24:MI'), 845);

INSERT INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ) 
VALUES (345, 'Gainesville', TO_DATE('12/10/2007 14:43', 'MM/DD/YYYY HH24:MI'), 845);

INSERT INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ) 
VALUES (345, 'Home Care', TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), 934);

INSERT INTO P (LAST_NAME, FIRST_NAME, MIDDLE_NAME, 
PID, PSEQ, FID) VALUES ('Smith', 'j', '', 1234, 345, 'F');






(SELECT

   p_name,
   pid,
   pseq,
   fid,
   branch,
   lseq,
   ORD,
   MINORD,
   perform_ddt,
   mcd
FROM
   (SELECT DISTINCT
      CASE
         WHEN (ORD = MINORD AND ORD = 7) THEN NULL
         ELSE rv
      END res,                        
      p_name,
      pid,
      pseq,
      fid,
      branch,
      rv,
      facility,
      lseq,
      ORD,
      MINORD,
      perform_ddt,
      mcd
   FROM
   (SELECT
      p_name,
      pid,
      pseq,
      fid,
      DDT.TOCHAR(perform_ddt,'MM/DD/YYYY HH24:MI') perform_ddt,
      DDT.TOCHAR(mcd,'MM/DD/YYYY HH24:MI') MCD,
      branch,
      rv,
      facility,
      ORD,
      MIN(ord) OVER (PARTITION BY pseq) MINORD, --FIRST BRANCH CHARTING
      lseq
   FROM
      (SELECT
         p_name,
         pid,
         pseq,
         fid,
         mcd,
         perform_ddt,
         rv,
         branch,
         facility,
         DECODE(BRANCH,'Gainesville',1,'North Regional',2,'Shands',3,'No Branch Chosen',4,'Other',5,'CHC',6,7) ord,
         lseq
      FROM
         (SELECT DISTINCT 
            INITCAP(p.last_name || ', ' || p.first_name || ' ' || p.middle_name) p_Name,
            p.pid,
            p.pseq,
            p.fid,
            FULLRES(pr.rv) rv,
            MAX(perform_ddt) OVER (PARTITION BY p.pseq) mcd,
            perform_ddt,
            CASE
               WHEN pr.rv LIKE '%G%' THEN 'Gainesville'
               WHEN pr.rv LIKE '%NR%' THEN 'North Regional'
               WHEN pr.rv LIKE '%S%' THEN 'Shands'
               WHEN pr.rv LIKE 'NBC' THEN 'No Branch Chosen'
               WHEN pr.rv LIKE 'CHC' THEN 'CHC'
               WHEN pr.lseq IN (934,966) THEN FULLRES(rv)
               ELSE 'Other'
            END Branch,
            p.fid Facility, 
            lseq
         FROM
            pr,
            p
         WHERE
            pr.pseq = p.pseq AND
            pr.lseq IN (845,932,934,966) AND 
            pr.pseq IN
               (SELECT /*+ PUSH_SUBQ */
                  pseq
               FROM
                  p
               WHERE
                  ddate > (TRUNC(ADD_MONTHS(SYSDATE,-1),'MM')))) ) ))
                  
WHERE
   RES IS NOT NULL AND
   ORD = MINORD  )
ORDER BY
   p_name,
   perform_ddt

[Updated on: Sat, 05 January 2008 17:59]

Report message to a moderator

Re: Excluding records in result set [message #291682 is a reply to message #291677] Sat, 05 January 2008 23:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
You have some extra commas in your create table statements and you should probably be using varchar2 instead of char. There are numerous errors in your query, so I will focus on the data set that you provided and the description of what you want instead. I added some extra rows so that there would be something left once all the undesirable rows were excluded. I created a view to match the data set that you provided and selected from that view. You could use inline views or a subquery factoring clause instead. There are always various ways to do things. When you are searching for a condition in one row that is dependent on a condition in another row, then you can use the EXISTS clause. When you want to subtract one set of data from another, you can use MINUS. The following may not be exactly what you want, but it should demonstrate the general concept that you can adapt to more exactly suit your needs. I have broken it down step by step.

-- corrected create table and insert statements with extra rows:
SCOTT@orcl_11g> CREATE TABLE PR (
  2  	PSEQ	     NUMBER(3),
  3  	RV	     VARCHAR2(30),
  4  	PERFORM_DDT  DATE,
  5  	LSEQ	     NUMBER(3))
  6  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
  3    VALUES (345, 'Gainesville', TO_DATE('12/10/2007 13:00', 'MM/DD/YYYY HH24:MI'), 845)
  4  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
  5    VALUES (345, 'Gainesville', TO_DATE('12/10/2007 14:43', 'MM/DD/YYYY HH24:MI'), 845)
  6  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
  7    VALUES (345, 'Home Care', TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), 934)
  8  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
  9    VALUES (345, 'North Regional', TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), 845)
 10  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
 11    VALUES (345, 'Shands', TO_DATE('12/12/2007 15:43', 'MM/DD/YYYY HH24:MI'), 845)
 12  INTO PR (PSEQ, RV, PERFORM_DDT, LSEQ)
 13    VALUES (345, 'Home Care', TO_DATE('12/11/2007 15:43', 'MM/DD/YYYY HH24:MI'), 845)
 14  SELECT * FROM DUAL
 15  /

6 rows created.

SCOTT@orcl_11g> CREATE TABLE P (
  2  	LAST_NAME    VARCHAR2(10),
  3  	FIRST_NAME   VARCHAR2(10),
  4  	MIDDLE_NAME  VARCHAR2(10),
  5  	PID	     NUMBER(4),
  6  	PSEQ	     NUMBER(3),
  7  	FID	     VARCHAR2(1))
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO P (LAST_NAME, FIRST_NAME, MIDDLE_NAME, PID, PSEQ, FID)
  2    VALUES ('Smith', 'j', '', 1234, 345, 'F')
  3  /

1 row created.


-- your data:
SCOTT@orcl_11g> SELECT * FROM pr
  2  /

      PSEQ RV                             PERFORM_DDT            LSEQ
---------- ------------------------------ ---------------- ----------
       345 Gainesville                    12/10/2007 13:00        845
       345 Gainesville                    12/10/2007 14:43        845
       345 Home Care                      12/12/2007 15:43        934
       345 North Regional                 12/12/2007 15:43        845
       345 Shands                         12/12/2007 15:43        845
       345 Home Care                      12/11/2007 15:43        845

6 rows selected.

SCOTT@orcl_11g> SELECT * FROM p
  2  /

LAST_NAME  FIRST_NAME MIDDLE_NAM        PID       PSEQ FID
---------- ---------- ---------- ---------- ---------- ---
Smith      j                           1234        345 F



-- view created from your data to match data set you provided with extra rows:
SCOTT@orcl_11g> CREATE OR REPLACE VIEW your_view AS
  2  SELECT p.last_name || ', ' || p.first_name AS p_name,
  3  	    p.pid,
  4  	    p.pseq,
  5  	    p.fid,
  6  	    pr.rv AS branch,
  7  	    pr.lseq,
  8  	    DECODE (pr.rv, 'Gainesville', 1, 'North Regional', 2, 'Shands', 3, 'No Branch Chosen', 4, 'Other', 5, 'CHC', 6, 7) ord,
  9  	    MIN (DECODE (pr.rv, 'Gainesville', 1, 'North Regional', 2, 'Shands', 3, 'No Branch Chosen', 4, 'Other', 5, 'CHC', 6, 7)) OVER
 10  	      (PARTITION BY p.pseq) AS minord,
 11  	    pr.perform_ddt,
 12  	    MAX (pr.perform_ddt) OVER (PARTITION BY p.pseq) AS mcd
 13  FROM   pr, p
 14  WHERE  pr.pseq = p.pseq
 15  /

View created.

SCOTT@orcl_11g> COLUMN p_name FORMAT A8
SCOTT@orcl_11g> COLUMN fid    FORMAT A3
SCOTT@orcl_11g> COLUMN branch FORMAT A14
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  ORDER  BY p_name, perform_ddt
  4  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 13:00 12/12/2007 15:43
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 14:43 12/12/2007 15:43
Smith, j       1234        345 F   Home Care             845          7          1 12/11/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   North Regional        845          2          1 12/12/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   Shands                845          3          1 12/12/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   Home Care             934          7          1 12/12/2007 15:43 12/12/2007 15:43

6 rows selected.


-- rows where ord = 7 and perform_ddt = mcd:
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  WHERE  ord = 7 AND perform_ddt = mcd
  4  ORDER  BY p_name, perform_ddt
  5  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   Home Care             934          7          1 12/12/2007 15:43 12/12/2007 15:43



-- rows where ord = minord and perform_ddt != mcd:
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  WHERE  ord = minord AND perform_ddt != mcd
  4  ORDER  BY p_name, perform_ddt
  5  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 13:00 12/12/2007 15:43
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 14:43 12/12/2007 15:43


-- rows where both conditions exist:
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view a
  3  WHERE  ord = 7 AND perform_ddt = mcd
  4  AND    EXISTS
  5  	    (SELECT *
  6  	     FROM   your_view b
  7  	     WHERE  b.pseq = a.pseq
  8  	     AND    ord = minord AND perform_ddt != mcd)
  9  UNION ALL
 10  SELECT *
 11  FROM   your_view a
 12  WHERE  ord = minord AND perform_ddt != mcd
 13  AND    EXISTS
 14  	    (SELECT *
 15  	     FROM   your_view b
 16  	     WHERE  b.pseq = a.pseq
 17  	     AND    ord = 7 AND perform_ddt = mcd)
 18  ORDER  BY 1, 9
 19  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 13:00 12/12/2007 15:43
Smith, j       1234        345 F   Gainesville           845          1          1 12/10/2007 14:43 12/12/2007 15:43
Smith, j       1234        345 F   Home Care             934          7          1 12/12/2007 15:43 12/12/2007 15:43


-- subtract records where both conditions exist:
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  MINUS
  4  (SELECT *
  5   FROM   your_view a
  6   WHERE  ord = 7 AND perform_ddt = mcd
  7   AND    EXISTS
  8  	     (SELECT *
  9  	      FROM   your_view b
 10  	      WHERE  b.pseq = a.pseq
 11  	      AND    ord = minord AND perform_ddt != mcd)
 12   UNION ALL
 13   SELECT *
 14   FROM   your_view a
 15   WHERE  ord = minord AND perform_ddt != mcd
 16   AND    EXISTS
 17  	     (SELECT *
 18  	      FROM   your_view b
 19  	      WHERE  b.pseq = a.pseq
 20  	      AND    ord = 7 AND perform_ddt = mcd))
 21  ORDER  BY 1, 9
 22  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   Home Care             845          7          1 12/11/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   North Regional        845          2          1 12/12/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   Shands                845          3          1 12/12/2007 15:43 12/12/2007 15:43



-- also subtract records where ord = 7 and another record exists
where ord != 7:
SCOTT@orcl_11g> SELECT *
  2  FROM   your_view
  3  MINUS
  4  (SELECT *
  5   FROM   your_view a
  6   WHERE  ord = 7 AND perform_ddt = mcd
  7   AND    EXISTS
  8  	     (SELECT *
  9  	      FROM   your_view b
 10  	      WHERE  b.pseq = a.pseq
 11  	      AND    ord = minord AND perform_ddt != mcd)
 12   UNION ALL
 13   SELECT *
 14   FROM   your_view a
 15   WHERE  ord = minord AND perform_ddt != mcd
 16   AND    EXISTS
 17  	     (SELECT *
 18  	      FROM   your_view b
 19  	      WHERE  b.pseq = a.pseq
 20  	      AND    ord = 7 AND perform_ddt = mcd)
 21   UNION ALL
 22   SELECT *
 23   FROM   your_view a
 24   WHERE  ord = 7
 25   AND    EXISTS
 26  	     (SELECT *
 27  	      FROM   your_view b
 28  	      WHERE  b.pseq = a.pseq
 29  	      AND    ord != 7))
 30  ORDER  BY 1, 9
 31  /

P_NAME          PID       PSEQ FID BRANCH               LSEQ        ORD     MINORD PERFORM_DDT      MCD
-------- ---------- ---------- --- -------------- ---------- ---------- ---------- ---------------- ----------------
Smith, j       1234        345 F   North Regional        845          2          1 12/12/2007 15:43 12/12/2007 15:43
Smith, j       1234        345 F   Shands                845          3          1 12/12/2007 15:43 12/12/2007 15:43

SCOTT@orcl_11g> 

Re: Excluding records in result set [message #291734 is a reply to message #291682] Sun, 06 January 2008 06:59 Go to previous messageGo to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Barbara,

What are the numerous errors in my query....that I might learn?

Thanks,
Stan

[Updated on: Sun, 06 January 2008 09:38]

Report message to a moderator

Re: Excluding records in result set [message #291773 is a reply to message #291734] Sun, 06 January 2008 13:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
staann56 wrote on Sun, 06 January 2008 04:59

Barbara,

What are the numerous errors in my query....that I might learn?

Thanks,
Stan



All you have do to is run the query to see the errors messages. However, I don't know whether you typed or copied and pasted, so some errors may just be due to typing errors. There may also be user-defined functions and/or variables that you failed to supply. The first error that you would receive would be due to having blank lines in your code. If you remove the blank lines, the next error that you get is about fullres. Assuming that is some user-defined function, if you comment it out in order to test the rest of the code, the next error that you get is about ddate. What is ddate? Is that a user-defined variable or should that have been perform_ddt or what? If you change that to perform_ddt to continue, then you get an error about ddt.tochar. What is ddt.tochar supposed to be? If you assume that ddt.tochar should have been just to_char, then you can finally run the query and get results, but apparently not the results that you want. You need to test your code before you post it and copy and paste a run of the code, complete with line numbers and error messages and/or results. The only reason that I tried to do anything with this mess is because I know that you are a newbie and this was your third attempt over a few days to post an understandable question.
Re: Excluding records in result set [message #291774 is a reply to message #291773] Sun, 06 January 2008 14:00 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Barbara,

Thanks. Most of the errors you received are due to the lack of functions that exist on our system. I apologize for the mess and will do a better job of supplying meaningful data and code.

thanks,
Stan

[Updated on: Sun, 06 January 2008 14:02]

Report message to a moderator

Previous Topic: large data deletion
Next Topic: sequence in package
Goto Forum:
  


Current Time: Sat Dec 10 18:58:12 CST 2016

Total time taken to generate the page: 0.06367 seconds