sqloracle-databasecase-statement

Oracle SQL only: Case statement or exists query to show results based on condition

I am trying to create computed column based on some conditions by using case statement. I am so close to goal but unable to see where the query is going wrong. Hope I get some best/easier methods and some help here.

Below are the tables:
PERSON Table:

+----+--------+
| ID | PERSON |
+----+--------+
|  1 | John   |
|  2 | Scott  |
|  3 | Ruth   |
|  4 | Smith  |
|  5 | Frank  |
|  6 | Martin |
|  7 | Blake  |
+----+--------+

ROLE Table:

+----+------+
| ID | ROLE |
+----+------+
|  1 | JJJ  |
|  2 | Auth |
|  3 | AAA  |
|  4 | MMM  |
|  5 | KKK  |
|  6 | BBB  |
+----+------+

and last one is the detail table
PERSON_ROLE Table:

+----+-----------+---------+
| ID | PERSON_ID | ROLE_ID |
+----+-----------+---------+
|  1 |         1 |       1 |
|  2 |         2 |       2 |
|  3 |         2 |       3 |
|  4 |         2 |       4 |
|  5 |         3 |       1 |
|  6 |         3 |       5 |
|  7 |         4 |       3 |
|  8 |         5 |       6 |
|  9 |         6 |       3 |
| 10 |         6 |       6 |
| 11 |         6 |       2 |
| 12 |         7 |       5 |
| 13 |         7 |       6 |
+----+-----------+---------+

Desired/Expected output:

+--------+--------+----------+
| PERSON | MYROLE | MYACCESS |
+--------+--------+----------+
| John   | JJJ    |          |
| Scott  | Auth   | Remove   |
| Scott  | AAA    |          |
| Scott  | MMM    |          |
| Ruth   | JJJ    |          |
| Ruth   | KKK    |          |
| Smith  | AAA    | Add      |
| Frank  | BBB    | Add      |
| Martin | AAA    |          |
| Martin | BBB    |          |
| Martin | Auth   | Remove   |
| Blake  | KKK    |          |
| Blake  | BBB    | Add      |
+--------+--------+----------+

Below are the conditions:

  1. If Person has Role "AAA" or "BBB" and not "Auth" then MYAccess column should show value as "Add" for that Person. All other values should be null.
  2. If Person has Role "Auth" then MYAccess column should show "Remove" only for that row. Even if same Person has "AAA" or "BBB" or any other value it should show null.

Below is the actual output I am getting which is partially correct:

+--------+--------+----------+
| PERSON | MYROLE | MYACCESS |
+--------+--------+----------+
| Blake  | KKK    |          |
| Blake  | BBB    | Add      |
| Frank  | BBB    | Add      |
| John   | JJJ    |          |
| Martin | AUTH   | Remove   |
| Martin | BBB    | Add      |
| Martin | AAA    | Add      |
| Ruth   | JJJ    |          |
| Ruth   | KKK    |          |
| Scott  | AAA    | Add      |
| Scott  | AUTH   | Remove   |
| Scott  | MMM    |          |
| Smith  | AAA    | Add      |
+--------+--------+----------+

For Persons Martin and Scott, it should show only "Remove" but I am getting "Add" as well.

Below is the query:

SELECT p.person,upper(r.role) myrole,
case when p.person in (select p.person from  person ut where ut.person = p.person and upper(r.role) = upper('Auth')) then 'Remove' 
     when p.person in (select p.person from  person ut where ut.person = p.person and (upper(r.role) = upper('Auth') or (upper(r.role) = upper('AAA') or upper(r.role) = upper('BBB')))) then 'Add' else null
end as myaccess
FROM person p
       join person_role pr
         ON p.id = pr.person_id
       join myrole r
         ON r.id = pr.role_id
order by p.person

DDL Script:

CREATE TABLE person (
    id      INTEGER NOT NULL,
    person  VARCHAR2(50 CHAR)
);

INSERT INTO person (
    id,
    person
) VALUES (
    1,
    'John'
);

INSERT INTO person (
    id,
    person
) VALUES (
    2,
    'Scott'
);

INSERT INTO person (
    id,
    person
) VALUES (
    3,
    'Ruth'
);

INSERT INTO person (
    id,
    person
) VALUES (
    4,
    'Smith'
);

INSERT INTO person (
    id,
    person
) VALUES (
    5,
    'Frank'
);

INSERT INTO person (
    id,
    person
) VALUES (
    6,
    'Martin'
);

INSERT INTO person (
    id,
    person
) VALUES (
    7,
    'Blake'
);

ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY ( id );


CREATE TABLE myrole (
    id    INTEGER NOT NULL,
    role  VARCHAR2(50 CHAR)
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    1,
    'JJJ'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    2,
    'Auth'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    3,
    'AAA'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    4,
    'MMM'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    5,
    'KKK'
);

INSERT INTO myrole (
    id,
    role
) VALUES (
    6,
    'BBB'
);

ALTER TABLE myrole ADD CONSTRAINT myrole_pk PRIMARY KEY ( id );


CREATE TABLE person_role (
    id         INTEGER NOT NULL,
    person_id  INTEGER,
    myrole_id  INTEGER
);

ALTER TABLE person_role ADD CONSTRAINT person_role_pk PRIMARY KEY ( id );

CREATE SEQUENCE myrole_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER myrole_tr BEFORE
    INSERT ON myrole
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := myrole_seq.nextval;
END;
/

CREATE SEQUENCE person_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_tr BEFORE
    INSERT ON person
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_seq.nextval;
END;
/

CREATE SEQUENCE person_role_seq START WITH 1 NOCACHE;

CREATE OR REPLACE TRIGGER person_role_tr BEFORE
    INSERT ON person_role
    FOR EACH ROW
    WHEN ( new.id IS NULL )
BEGIN
    :new.id := person_role_seq.nextval;
END;
/

Thanks
Richa

Solution

  • is this what you're looking for ?
    In your conditions you're saying: "If Person has Role "AAA" or "BBB" and not "Auth" then MYAccess column should show value as "Add" for that Person. All other values should be null. " Scott has Auth so this condition yields false. According to this rule Scott should not have "Add". In your expected output it has "Add". Am I missing something ?

    WITH person_roles (id, person_id, role_id) AS
(
SELECT  1, 1,1 FROM DUAL UNION ALL
SELECT  2, 2,2 FROM DUAL UNION ALL
SELECT  3, 2,3 FROM DUAL UNION ALL
SELECT  4, 2,4 FROM DUAL UNION ALL
SELECT  5, 3,1 FROM DUAL UNION ALL
SELECT  6, 3,5 FROM DUAL UNION ALL
SELECT  7, 4,3 FROM DUAL UNION ALL
SELECT  8, 5,6 FROM DUAL UNION ALL
SELECT  9, 6,3 FROM DUAL UNION ALL
SELECT 10, 6,6 FROM DUAL UNION ALL
SELECT 11, 6,2 FROM DUAL UNION ALL
SELECT 12, 7,5 FROM DUAL UNION ALL
SELECT 13, 7,6 FROM DUAL
),persons (id, person) AS
(
SELECT   1,'John' FROM DUAL UNION ALL   
SELECT   2,'Scott' FROM DUAL UNION ALL  
SELECT   3,'Ruth' FROM DUAL UNION ALL   
SELECT   4,'Smith' FROM DUAL UNION ALL  
SELECT   5,'Frank' FROM DUAL UNION ALL  
SELECT   6,'Martin' FROM DUAL UNION ALL 
SELECT   7,'Blake' FROM DUAL
),roles (id, role) AS
(
SELECT 1,'JJJ' FROM DUAL UNION ALL   
SELECT 2,'Auth' FROM DUAL UNION ALL  
SELECT 3,'AAA' FROM DUAL UNION ALL   
SELECT 4,'MMM' FROM DUAL UNION ALL   
SELECT 5,'KKK' FROM DUAL UNION ALL   
SELECT 6,'BBB' FROM DUAL 
), rule_1(person_id, role_type)  AS
(
SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) 
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  GROUP BY p.id
)
SELECT p.person, r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as action
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  JOIN rule_1 rl ON rl.person_id = pr.person_id
ORDER BY p.person

PERSON ROLE ACTION
------ ---- ------
Blake  BBB  Add   
Blake  KKK        
Frank  BBB  Add   
John   JJJ        
Martin AAA        
Martin Auth Remove
Martin BBB        
Ruth   KKK        
Ruth   JJJ        
Scott  MMM        
Scott  AAA        
Scott  Auth Remove
Smith  AAA  Add

    If you want to avoid the CTE (with clause) you can replace the last 2 statements with 1:

    SELECT p.person, r.role, 
  CASE 
    WHEN rl.role_type = 1 AND r.role IN ('AAA','BBB') THEN 'Add'  
    WHEN rl.role_type = 0 AND r.role = 'Auth' THEN 'Remove' 
  END as action
  FROM person_roles pr 
  JOIN persons p ON p.id = pr.person_id 
  JOIN roles r ON r.id = pr.role_id
  JOIN (
    SELECT p.id, MIN(CASE WHEN r.ROLE = 'Auth' THEN 0 WHEN r.ROLE in ('AAA','BBB') THEN 1 ELSE 2 END) as role_type
    FROM person_roles pr 
    JOIN persons p ON p.id = pr.person_id 
    JOIN roles r ON r.id = pr.role_id
    GROUP BY p.id
    ) rl ON rl.id = pr.person_id
ORDER BY p.person