Home » SQL & PL/SQL » SQL & PL/SQL » finding status (12c)
finding status [message #665698] Fri, 15 September 2017 12:30 Go to next message
m.abdulhaq
Messages: 243
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 Go to previous messageGo to next message
_jum
Messages: 533
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 Go to previous message
quirks
Messages: 81
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';
Previous Topic: How to verify if string contains unwanted character
Next Topic: split name
Goto Forum:
  


Current Time: Mon Nov 19 01:26:29 CST 2018