how can i use 'CASE WHEN' syntax in Oracle?

Question

i want to manipulate query result

original query is

SELECT grantee,
               privilege
          FROM DBA_TAB_PRIVS
         WHERE OWNER = 'BIZOWNFO'
           AND TABLE_NAME = 'TB_AT_CASHREC_DTLS';

I want to make result like this query

select grantee,
       (case when privilege = 'SELECT' then 'O' end ) as SEL,
       (case when privilege = 'UPDATE' then 'O' end ) as UPD,
       (case when privilege = 'INSERT' then 'O' end ) as INS,
       (case when privilege = 'DELETE' then 'O' end ) as DEL
FROM
(
SELECT grantee,
   privilege
FROM DBA_TAB_PRIVS
WHERE OWNER = 'BIZOWNFO'
AND TABLE_NAME = 'TB_AT_CASHREC_DTLS'
) group by grantee;

but it didn't work

i want to know how to do it.

Are you using MySQL, MS SQL Server or Oracle? (Remove the unrelated tags.) — jarlh
– jarlh, Commented Apr 23, 2021 at 7:35

Tim Biegeleisen · Accepted Answer · 2021-04-23 07:38:21Z

2

It looks like you need the GROUP BY clause with pivoting logic:

SELECT
    grantee AS "ROLE",
    MAX(CASE WHEN privilege = 'SELECT' THEN 'O' END) AS "SELECT",
    MAX(CASE WHEN privilege = 'UPDATE' THEN 'O' END) AS "UPDATE",
    MAX(CASE WHEN privilege = 'INSERT' THEN 'O' END) AS "INSERT",
    MAX(CASE WHEN privilege = 'DELETE' THEN 'O' END) AS "DELETE"
FROM DBA_TAB_PRIVS
WHERE
    OWNER = 'BIZOWNFO' AND TABLE_NAME = 'TB_AT_CASHREC_DTLS'
GROUP BY
    grantee;

answered Apr 23, 2021 at 7:38

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kihyun94 Over a year ago

that is what i really want ! Thank you so much !

Littlefoot · Accepted Answer · 2021-04-23 07:36:44Z

0

Looks like you should remove the GROUP BY clause:

SQL> SELECT grantee,
  2         (CASE WHEN privilege = 'SELECT' THEN 'O' END) AS sel,
  3         (CASE WHEN privilege = 'UPDATE' THEN 'O' END) AS upd,
  4         (CASE WHEN privilege = 'INSERT' THEN 'O' END) AS ins,
  5         (CASE WHEN privilege = 'DELETE' THEN 'O' END) AS del
  6    FROM (SELECT grantee, privilege
  7            FROM dba_tab_privs
  8           WHERE     owner = 'MIKE'
  9                 AND table_name = 'TEST');

GRANTEE                        S U I D
------------------------------ - - - -
KCDBA                          O
SCOTT                              O

SQL>

answered Apr 23, 2021 at 7:36

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Collectives™ on Stack Overflow

how can i use 'CASE WHEN' syntax in Oracle?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related