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  |
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   |
 |
Barbara Boehmer
Messages: 9104 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 #291773 is a reply to message #291734] |
Sun, 06 January 2008 13:53   |
 |
Barbara Boehmer
Messages: 9104 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.
|
|
|
|
Goto Forum:
Current Time: Sun Feb 09 09:01:36 CST 2025
|