Home » SQL & PL/SQL » SQL & PL/SQL » finding status (12c)
finding status [message #665698] |
Fri, 15 September 2017 12:30 |
|
m.abdulhaq
Messages: 254 Registered: April 2013 Location: Ajman
|
Senior Member |
|
|
Hello experts,
I need to filter out those records from my table where all the three rows are having appr_sign column as not null, there can be two records or three depending on the po_no , number of records will inserted and i want to
fetch only those records whose all the three records are having appr_sign as not null.
CREATE TABLE PO_APPR(PO_NO NUMBER, PO_APPR_UID VARCHAR2(12))
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(1,'1001');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(1,'1002');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(1,'1003');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(2,'1001');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(2,'1007');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(2,NULL);
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(3,'1001');
INSERT INTO PO_APPR(PO_NO,PO_APPR_UID) VALUES(3,null);
--desired result will be only since po_no is having one row as null.
po_no
1
|
|
|
Re: finding status [message #665718 is a reply to message #665698] |
Mon, 18 September 2017 01:54 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
The example shows one approach. Count all rows and compare them with the NOT NULL rows per po_no, then filter out the wanted ones (here cntnn=3):
WITH PO_APPR(po_no,po_appr_uid) AS
(SELECT 1,'1001' FROM dual UNION ALL
SELECT 1,'1002' FROM dual UNION ALL
SELECT 1,'1003' FROM dual UNION ALL
SELECT 2,'1001' FROM dual UNION ALL
SELECT 2,'1007' FROM dual UNION ALL
SELECT 2, NULL FROM dual UNION ALL
SELECT 3,'1001' FROM dual UNION ALL
SELECT 3, NULL FROM dual)
SELECT po_no,po_appr_uid
--count all rows
, count(*) OVER (PARTITION BY po_no) cntall
--count rows with po_appr_uid IS NOT NULL
, count(po_appr_uid) OVER (PARTITION BY po_no) cntnn
FROM po_appr;
PO_NO PO_APPR_UID CNTALL CNTNN
------------------------------------
1 1001 3 3
1 1002 3 3
1 1003 3 3
2 1001 3 2
2 3 2
2 1007 3 2
3 1001 2 1
3 2 1
[Updated on: Mon, 18 September 2017 01:55] Report message to a moderator
|
|
|
Re: finding status [message #665739 is a reply to message #665718] |
Tue, 19 September 2017 05:51 |
|
quirks
Messages: 82 Registered: October 2014
|
Member |
|
|
WITH
PO_APPR(PO_NO, PO_APPR_UID)
AS
(SELECT 1, '1001' FROM DUAL UNION ALL
SELECT 1, '1002' FROM DUAL UNION ALL
SELECT 1, '1003' FROM DUAL UNION ALL
SELECT 2, '1001' FROM DUAL UNION ALL
SELECT 2, '1007' FROM DUAL UNION ALL
SELECT 2, NULL FROM DUAL UNION ALL
SELECT 3, '1001' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL),
FIND_VALID_ROWS
AS
(SELECT DISTINCT
PO_NO
,CASE
WHEN FIRST_VALUE(PO_APPR_UID) OVER(PARTITION BY PO_NO ORDER BY PO_APPR_UID NULLS FIRST) IS NULL THEN
'kill me' ELSE
'let me live'
END
FILTER_ME
FROM PO_APPR)
SELECT PO_NO
FROM FIND_VALID_ROWS
WHERE FILTER_ME = 'let me live';
|
|
|
Goto Forum:
Current Time: Sat Jun 29 06:36:25 CDT 2024
|