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:
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
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