This discussion is archived
8 Replies Latest reply: Jan 4, 2013 9:21 AM by rp0428 RSS

Query or functions to achieve this logic ??  Please help...

pbo Newbie
Currently Being Moderated
Hi all,

I've a table "permission" with the following data

Name Permission
A Read
B Read
A Write
C Execute
B Execute

There can be only three permissions for a user - Read , write and execute

Following is the required o/p : ( if the user has a permission (r/w/x) , the corresponding value will be 1 , otherwise it is 0 )

Name Read Write Execute
A 1 1 0
B 1 0 1
C 0 0 1

Are there any standard functions in Oracle 10 to achieve this o/p ? Or Please provide a query to achieve this ...

Thanks in Advance,
Praneeth.

Edited by: pbo on Jan 3, 2013 8:58 PM
  • 1. Re: Query or functions to achieve this logic ??  Please help...
    jeneesh Guru
    Currently Being Moderated
    select name,
       nvl(max(decode(permission,'Read',1,0)),0) read,
       nvl(max(decode(permission,'Write',1,0)),0) write,
       nvl(max(decode(permission,'Execute',1,0)),0) execute
    from permission
    group by name;
  • 2. Re: Query or functions to achieve this logic ??  Please help...
    pbo Newbie
    Currently Being Moderated
    Thanks for the reply...
    But is it possible to achieve this without using DECODE or IF/THEN ??
  • 3. Re: Query or functions to achieve this logic ??  Please help...
    Purvesh K Guru
    Currently Being Moderated
    One way of doing it:
    with data as
    (
      select 'A' name, 'Read' perm from dual union all
      select 'B' name, 'Read' perm from dual union all
      select 'A' name, 'Write' perm from dual union all
      select 'C' name, 'Execute' perm from dual union all
      select 'B' name, 'Execute' perm from dual
    )
    --End of sample data
    select name,
           max(decode(perm, 'Read', 1, 0)) read,
           max(decode(perm, 'Write', 1, 0)) write,
           max(decode(perm, 'Execute', 1, 0)) read
      from data --> You can use your table name instead of sample table
     group by name;
    
    NAME READ                   WRITE                  READ                   
    ---- ---------------------- ---------------------- ---------------------- 
    A    1                      1                      0                      
    B    1                      0                      1                      
    C    0                      0                      1
  • 4. Re: Query or functions to achieve this logic ??  Please help...
    Purvesh K Guru
    Currently Being Moderated
    jeneesh wrote:
    select name,
    nvl(max(decode(permission,'Read',1,0)),0) read,
    nvl(max(decode(permission,'Write',1,0)),0) write,
    nvl(max(decode(permission,'Execute',1,0)),0) execute
    from permission
    group by name;
    An NVL would not be required, right? Decode handles NULL.
  • 5. Re: Query or functions to achieve this logic ??  Please help...
    jeneesh Guru
    Currently Being Moderated
    pbo wrote:
    Thanks for the reply...
    But is it possible to achieve this without using DECODE or IF/THEN ??
    SQL> with permission
      2  as
      3  (
      4  select 'A' name, 'Read' permission from dual union all
      5  select 'B', 'Read' from dual union all
      6  select 'A', 'Write' from dual union all
      7  select 'C', 'Execute'  from dual union all
      8  select 'B', 'Execute' from dual
      9  ),
     10  reqd_data as
     11  (
     12   select name,permission
     13   from permission
     14  )
     15  select *
     16  from reqd_data
     17  pivot
     18    (
     19     count(*) for permission in ('Read','Write','Execute')
     20    );
    
    N     'Read'    'Write'  'Execute'
    - ---------- ---------- ----------
    A          1          1          0
    B          1          0          1
    C          0          0          1
  • 6. Re: Query or functions to achieve this logic ??  Please help...
    Purvesh K Guru
    Currently Being Moderated
    pbo wrote:
    Thanks for the reply...
    But is it possible to achieve this without using DECODE or IF/THEN ??
    You have alternative of PIVOT, introduced in Oracle 11g, but would not be helpful as you are still on Oracle 10g.

    Jeneesh has shown you the demonstration.
  • 7. Re: Query or functions to achieve this logic ??  Please help...
    EdStevens Guru
    Currently Being Moderated
    pbo wrote:
    Thanks for the reply...
    But is it possible to achieve this without using DECODE or IF/THEN ??
    and what's wrong with using DECODE or IF/THEN ??


    IF you find a proposed solution unacceptable, you need to state WHY so that people will (1) know the parameters of an acceptable solution, or (2) be able to explain why your objections are unfounded.
  • 8. Re: Query or functions to achieve this logic ??  Please help...
    rp0428 Guru
    Currently Being Moderated
    >
    IF you find a proposed solution unacceptable, you need to state WHY so that people will (1) know the parameters of an acceptable solution, or (2) be able to explain why your objections are unfounded.
    Especially since OP asked for standard functions to begin with.
    >
    Are there any standard functions in Oracle 10 to achieve this o/p ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points