OraFAQ Forum: SQL & PL/SQL » finding status

Home » SQL & PL/SQL » SQL & PL/SQL » finding status (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

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';

Report message to a moderator

Previous Topic:	How to verify if string contains unwanted character
Next Topic:	split name

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 16:18:12 CDT 2024