CREATE TABLE test_tab (
    e_id       NUMBER(10),
    sys_name   VARCHAR2(20),
    ref_id     NUMBER(10),
    sys_other  VARCHAR2(20)
);

INSERT INTO test_tab VALUES(805,'SRC',NULL,'Email');
INSERT INTO test_tab VALUES(805,'SRC',NULL,'Test');
INSERT INTO test_tab VALUES(805,'TGT',NULL,null);
INSERT INTO test_tab VALUES(805,'TGT',128,'Email');
INSERT INTO test_tab VALUES(6122,'SRC',NULL,'Email');
INSERT INTO test_tab VALUES(6122,'TGT',NULL,'Email');
INSERT INTO test_tab VALUES(122,'SRC',128,null);
INSERT INTO test_tab VALUES(122,'TGT',128,null);

COMMIT;

Tool used: Oracle Developer(18c)

I want to create one report wherein I need to include only those e_id whose ref_id is not null and sys_other is either Email or null. Other than this combination, that e_id will not be considered in the report. For ex:

e_id = 122 it should come in report as ref_id is not null and sys_other is null

e_id = 6122 it should not come in report as ref_id is null

e_id = 805 it should also not come in report where ref_id is not null but in sys_other we have value other than Email and null which is Test so we should exclude this e_id

Logic: Basically, need to check two columns ref_id and sys_other. If ref_id is null then we should exclude those e_id and if it is not null then need to check sys_other column and if it has 'Email' than include otherwise exclude irrespective of column sys_name

My Attempt:

SELECT e_id,ref_id,sys_other
FROM test_tab
WHERE ref_id IS NOT NULL
  AND (sys_other = 'Email' OR sys_other IS NULL)
GROUP BY e_id,ref_id,sys_other;

But the above query is giving me 805 id which should be excluded.

Expected output:

 ------ -------- ----------- 
| E_ID | REF_ID | SYS_OTHER |
 ------ -------- ----------- 
|  122 |    128 | null      |
 ------ -------- ----------- 

CodePudding user response：

You can try:

SELECT e_id,
       MAX(ref_id)    AS ref_id,
       MAX(sys_other) AS sys_other
FROM   test_tab
GROUP BY e_id
HAVING COUNT(ref_id) > 0
AND    COUNT(CASE WHEN sys_other <> 'Email' THEN 1 END) = 0;

Which will group by each e_id and for each group:

• count the non-null ref_id and ensure that there is as least 1 row and
• count the non-null sya_other values that are not Email and ensure that there are no rows that are non-null, non-email rows in the group.

CodePudding user response：

You can exclude the e_id acording to conditions stated in Logic:

WITH
    exclude AS
        (
            SELECT
                E_ID,
                SYS_OTHER
            FROM
                TEST_TAB
            WHERE
                SYS_OTHER Is Not Null And SYS_OTHER <> 'Email'
            GROUP BY
                E_ID,
                SYS_OTHER
        )

SELECT 
    e_id,
    ref_id,
    sys_other
FROM 
    test_tab
WHERE 
    E_ID Not IN(SELECT E_ID FROM exclude) And
    ref_id IS NOT NULL AND 
    Nvl(sys_other, 'Email') = 'Email'
GROUP BY 
    e_id,
    ref_id,
    sys_other;