Solved

Grouping on a multiple columns in oracle 11g

Posted on 2015-02-19
7
169 Views
Last Modified: 2015-02-20
Hello,
I have attached an example document that shows of an output I would like to see.
Here is the query that I've been trying to use.  Is this where grouping can come in handy
or any other statements that can be used.
Thanks


SELECT act.act_first_name || ' ' || acr1.act_last_name "Actor",
  acr1.rol_code "Role",
  arg.arg_rgp_code "Rights Group",
  act.act_manager_code "Manager"
FROM v_act_actor act
LEFT JOIN v_acr_actor_role_1 acr1 ON act.act_id = acr1.acr_act_id
LEFT JOIN v_arg_act_rightgroup arg ON act.act_id = arg.arg_act_id
WHERE act.act_status     = 'A'
AND act.act_manager_code = '49364'
Actor-grouping-example.doc
0
Comment
Question by:Machinegunner
  • 3
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40620717
Can you give some sample data with table structure ?
0
 

Author Comment

by:Machinegunner
ID: 40621086
Thanks for the reply,
Sample data attached.
Table structure are that all the columns are varchar2(x) as the datatypes.
v_act_actor
(Actor                varchar2(50),
  Role                  varchar2(250),
  Rights_Group  varchar2(250),
  Manager          varchar2(40))

Let's go with a simpler select without any joins like below:

SELECT Actor,
  Role,
  Rights_Group,
  Manager
FROM v_act_actor act
WHERE act_status  = 'A'
AND act_manager_code = '49364'
Sample-Data---Actors.xls
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 40621177
So, you want multiple lines of output (if the Actor has multiple Roles or Rights_Group records)  but you want the Actor's name to print only on the first line of output for that Actor, correct?

If you run your query in SQL*Plus, use this command first:
break on actor

Also, you should add an "order by" clause, something like this:
order by actor, role, rights_group

(Remember that in Oracle queries, if you don't add an explicit "order by" clause, the order that the records are displayed to you in will usually not match what you want or expect.)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 16

Accepted Solution

by:
Swadhin Ray earned 500 total points
ID: 40621243
Here is the sample data and table structure I created , assume that the datatype can be changed but the data are ok to test :

create table v_act_actor
(
act_first_name varchar2(100)
,act_manager_code number 
,act_status varchar2(1)
,act_id number 
) ;


create table v_acr_actor_role_1
(
act_last_name varchar2(100)
,rol_code varchar2(100)
,acr_act_id number 
) ; 


create table v_arg_act_rightgroup
(
arg_rgp_code varchar2(100)
,arg_act_id number 
);

begin
insert into v_act_actor values('Terry',49364,'A', 1  ) ; 
insert into v_act_actor values('Steven',49364,'A', 2  ) ; 
insert into v_acr_actor_role_1 values('Burns', 'INTERNAL ACTOR',1 );
insert into v_acr_actor_role_1 values('Burns', 'QE',1 );
insert into v_acr_actor_role_1 values('Burns', 'DE',1 );
insert into v_acr_actor_role_1 values('Wallaby', 'INTERNAL ACTOR',2 );
insert into v_acr_actor_role_1 values('Wallaby', 'QE',2 );
insert into v_acr_actor_role_1 values('Wallaby', 'QA',2 );
insert into v_arg_act_rightgroup values ('QE', 1) ; 
insert into v_arg_act_rightgroup values ('UNIVERSAL', 1) ; 
insert into v_arg_act_rightgroup values ('DE', 1) ; 
insert into v_arg_act_rightgroup values ('QA', 2) ; 
insert into v_arg_act_rightgroup values ('QE', 2) ; 
insert into v_arg_act_rightgroup values ('DE', 2) ; 
commit;
end;

Open in new window


Here is the SQL:

create or replace view grp_v as 
with t1 as
(select a.act_first_name || b.act_last_name as Actor,
b.rol_code,b.acr_act_id,
a.act_manager_code,
row_number() over (partition by b.act_last_name order by a.act_first_name )as rno
from v_act_actor a, v_acr_actor_role_1 b 
where a.act_id = b.acr_act_id),
t2 as
(select c.arg_rgp_code,c.arg_act_id,
row_number() over (partition by c.arg_act_id order by c.arg_act_id)as rno
from v_arg_act_rightgroup c)
select t1.Actor ,
       t1.rol_code ,
       t2.arg_rgp_code,
       t1.act_manager_code 
  from t1,t2
  where t1.rno=t2.rno
  and t1.acr_act_id = t2.arg_act_id;

  
  
  
  select case row_number() over(partition by Actor
   order by rol_code
   )  when 1 then Actor
             else null
end Actor ,
rol_code as "Role", 
arg_rgp_code as "Rights_Group",
act_manager_code as "Manager"
      from grp_v ; 
      

Open in new window

0
 

Author Comment

by:Machinegunner
ID: 40621244
Thanks for the reply.
Yes, I would like it to break on Actor and I did try the 'break on' command but would like something
similar to work within either SqlDeveloper or Toad.

Can this be done within these apps other than sqlplus?
0
 
LVL 16

Expert Comment

by:Swadhin Ray
ID: 40621289
Did you checked the SQL which I have provided.
0
 

Author Closing Comment

by:Machinegunner
ID: 40621373
Thanks!   I created a temp table instead as didn't have permissions to create a view using the account that I was provided.
I was able to run afterwards.
Thanks again,
MG
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now