DB2 Query to pick hierarchy values


 
Thread Tools Search this Thread
Top Forums Programming DB2 Query to pick hierarchy values
# 1  
Old 08-21-2018
DB2 Query to pick hierarchy values

Dear Team

I am using DB2 v9 .
I have a condition to check roles based on hierarchies like below example.

1.Ramesh has Roles as "Manager" and "Interviewer"
2.KITS has Roles as "Interviewer"
3.ANAND has Roles as "Manager" and "Interviewer"


select * FROM TESTING

Code:
NAME	          ROLE_ID	                  ROLE_NAME
Ramesh              	10	                   MANAGER                                           
Ramesh              	11	                   INTERVIEWER                                       
KITS                  	11	                   INTERVIEWER                                       
ANAND               	10	                   MANAGER                                           
ANAND               	11	                   INTERVIEWER

Expected Output:
Code:
NAME	           ROLE_ID	ROLE_NAME
Ramesh               	10	         MANAGER                                           
ANAND               	10             MANAGER      
KITS                  	11      	INTERVIEWER

How to get the name for roles having as "Manager" firstly and then other roles "Interviewer"

Thanks

Last edited by Perlbaby; 08-21-2018 at 11:25 AM..
# 2  
Old 08-21-2018
Dear Perlbaby,

I have a few to questions pose in response first:-
  • Is this homework/assignment? There are specific forums for these.
  • What have you tried so far?
  • What output/errors do you get?
  • What OS and version are you using?
  • What logical process have you considered? (to help steer us to follow what you are trying to achieve)
Most importantly, What have you tried so far?

There are probably many ways to achieve most tasks, so giving us an idea of your style and thoughts will help us guide you to an answer most suitable to you so you can adjust it to suit your needs in future.


We're all here to learn and getting the relevant information will help us all.


Thanks, in advance,
Robin
# 3  
Old 08-22-2018
Dear Robin
1. This is just learning exercise
2. I did try to rank option but looking for better approach
3. Looking for other option
4.Windows 10, DB2 v9
5.NA
Well I ranked based on roles first and have stored as reference tables. Since the roles may increases and example posted above has only 2 role examples .

Just to clear - I am new to DB2 and hope to learn from this post. Between I don't need exact code but the idea or heads up would work

Thanks
# 4  
Old 08-22-2018
Check if this fills your requirement :
Code:
select * from testing ;
Ramesh|10|MANAGER
Ramesh|11|INTERVIEWER
Anand|11|INTERVIEWER
Anand|10|MANAGER
kits|11|INTERVIEWER
Peasant|12|EMPLOYEE
Terminator|5|MACHINE
Terminator|10|MANAGER

select * from testing order by (
    case role_name

    when 'MANAGER'
    then 1

    when 'INTERVIEWER'
    then 2

    when 'MACHINE'
    then 3

    when 'EMPLOYEE'
    then 4

    end
) asc;
Ramesh|10|MANAGER
Anand|10|MANAGER
Terminator|10|MANAGER
Ramesh|11|INTERVIEWER
Anand|11|INTERVIEWER
kits|11|INTERVIEWER
Terminator|5|MACHINE
Peasant|12|EMPLOYEE

Regards
Peasant

Last edited by Peasant; 08-22-2018 at 09:07 AM.. Reason: Changed case to uppercase input.
# 5  
Old 08-23-2018
Hello Peasant.
Thanks for the reply.
But the expectation is different. If the person contains both roles say manager and interviewer. It should show only one record with manager. Please see below expected results. If no manager then name should pick next hierarchy interviewer.
Code:
NAME ROLE_IDROLE_NAME
Ramesh   10  MANAGER                                           
ANAND    10  MANAGER      
 KITS.         11 Interviewer.



Moderator's Comments:
Mod Comment Please use CODE tags as required by forum rules!

Last edited by RudiC; 08-23-2018 at 03:50 AM.. Reason: Added CODE tags.
# 6  
Old 08-27-2018
Quote:
Originally Posted by Perlbaby
...
If the person contains both roles say manager and interviewer. It should show only one record with manager. Please see below expected results. If no manager then name should pick next hierarchy interviewer.
Code:
NAME ROLE_IDROLE_NAME
Ramesh   10  MANAGER                                           
ANAND    10  MANAGER      
 KITS.         11 Interviewer.

...
What you want are the "rank()" or "dense_rank()" analytic functions.
They could be referred to as "window functions" or "OLAP functions" in DB2 and some of them are available in DB2 9.7 at least.
Not sure if your version has it.

The queries below are in Oracle and they should work in DB2 as well.
Using the "rank()" function, I take a name ("partition by name") and order all records by role_id ("order by role_id").
So, for "Ramesh" the lowest role_id is 10 - that record has rank 1.
For "Ramesh", the next role_id is 11 - that record has rank 2.

Once the name changes, the rank is reset to 1.
Now for "KITS", the lowest role_id is 11 - so that record has rank 1.
And so on...

Code:
SQL>
SQL> select * from testing;

NAME                              ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
Ramesh                                 10 MANAGER
Ramesh                                 11 INTERVIEWER
KITS                                   11 INTERVIEWER
ANAND                                  10 MANAGER
ANAND                                  11 INTERVIEWER

5 rows selected.

SQL>
SQL> select name, role_id, role_name,
  2         rank() over (partition by name order by role_id) as rnk
  3    from testing
  4  ;

NAME                              ROLE_ID ROLE_NAME              RNK
------------------------------ ---------- --------------- ----------
ANAND                                  10 MANAGER                  1
ANAND                                  11 INTERVIEWER              2
KITS                                   11 INTERVIEWER              1
Ramesh                                 10 MANAGER                  1
Ramesh                                 11 INTERVIEWER              2

5 rows selected.

SQL>
SQL>

Once you have data in this form, all you have to do is to pick up records with rnk = 1 in an outer query. (You cannot pick them up in the same query in the "where" clause.)

Code:
SQL>
SQL> with t as (
  2      select name, role_id, role_name,
  3             rank() over (partition by name order by role_id) as rnk
  4        from testing
  5  )
  6  select name, role_id, role_name
  7    from t
  8   where rnk = 1
  9   order by role_id
 10  ;

NAME                              ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND                                  10 MANAGER
Ramesh                                 10 MANAGER
KITS                                   11 INTERVIEWER

3 rows selected.

SQL>
SQL>

In case your DB2 version does not understand that "with" clause, then try a subquery:

Code:
SQL>
SQL> select t.name, t.role_id, t.role_name
  2    from (
  3              select name, role_id, role_name,
  4                     rank() over (partition by name order by role_id) as rnk
  5                from testing
  6         ) t
  7   where t.rnk = 1
  8   order by t.role_id
  9  ;

NAME                              ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND                                  10 MANAGER
Ramesh                                 10 MANAGER
KITS                                   11 INTERVIEWER

3 rows selected.

SQL>
SQL>

(You may need to add the keyword "as" between ")" and "t" in line 6.)

And in case your DB2 version does not understand analytic functions at all, then we have to go back 10 to 15 years in time. Smilie
Back in the day, we used the good old "exists" clause and a self-join to solve this problem.

Code:
SQL>
SQL> --
SQL> -- Pick up the row from "x" for which there is no row in "y" (same table)
SQL> -- with the same name but lower role_id. That is, pick up the row from "x" with
SQL> -- the lowest role_id for each name.
SQL> --
SQL> select x.name, x.role_id, x.role_name
  2    from testing x
  3   where not exists (select null
  4                       from testing y
  5                      where y.name = x.name
  6                        and y.role_id < x.role_id
  7                    )
  8   order by x.role_id
  9  ;

NAME                              ROLE_ID ROLE_NAME
------------------------------ ---------- ---------------
ANAND                                  10 MANAGER
Ramesh                                 10 MANAGER
KITS                                   11 INTERVIEWER

3 rows selected.

SQL>
SQL>

You can immediately see how powerful analytic functions are compared to the query above. Analytic functions scan the table once; the "exists" query scans the table more than once. There are many other advantages as well. Many extremely convoluted queries from the "old" SQL can be written in a trivial manner using analytic functions of the "new" SQL.

Cheers!

Last edited by durden_tyler; 08-27-2018 at 01:16 PM..
These 2 Users Gave Thanks to durden_tyler For This Post:
# 7  
Old 08-28-2018
Hello @durden_tyler
Superb !! This works perfectly in db2 v10. I like the way you elaborated - crystal clear walkthrough for this scenario handling. Thanks again for your patience .
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

DB2 Query modification to remove duplicate values using LISTAGG function

I am using DB2 v9 and trying to get country values in comma seperated format using below query SELECT distinct LISTAGG(COUNTRIES, ',') WITHIN GROUP(ORDER BY EMPLOYEE) FROM LOCATION ; Output Achieved MEXICO,UNITED STATES,INDIA,JAPAN,UNITED KINGDOM,MEXICO,UNITED STATES The table... (4 Replies)
Discussion started by: Perlbaby
4 Replies

2. Programming

DB2 Query -Convert multi values from column to rows

Hi Team I am using DB2 artisan tool and struck to handle multi values present in columns that are comma(,) separated. I want to convert those column values in separate rows . For example : Column 1 Column2 Jan,Feb Hold,Sell,Buy Expected Result Column1 ... (3 Replies)
Discussion started by: Perlbaby
3 Replies

3. Shell Programming and Scripting

Db2 query on other host

Hello, i need some help with a script. I made a script, which connect to different hosts to get some informations. But i got now some problems with getting informations of a database (db2) which is on a other host. I tried something like var=$(rsh HOST su - db2adm -c "db2 connect to database;... (2 Replies)
Discussion started by: Cyver
2 Replies

4. Shell Programming and Scripting

awk file to read values from Db2 table replacing hard coded values

Hi, I want to replace a chain of if-else statement in an old AWK file with values from Db2 table or CSV file. The part of code is below... if (start_new_rec=="true"){ exclude_user="false"; user=toupper($6); match(user, "XXXXX."); if (RSTART ==2 ) { ... (9 Replies)
Discussion started by: asandy1234
9 Replies

5. Shell Programming and Scripting

Return db2 query value to shell script

Hi, Im new to DB2. I need to connect to DB2 from shell script and return the query output back to shell script variable. this is my code #!/bin/ksh db_name=db db_user=usr db_pwd=pwd db2 <<EOSQL connect to $db_name user $db_user using "$db_pwd" select count(1) from table quit EOSQL ... (3 Replies)
Discussion started by: sup
3 Replies

6. AIX

DB2 user query utilization?

I am new to AIX, but not new to the UNIX world. Recently I got a request to come with some kind of report to do the following: report if user query is using > 70% CPU (within DB2) > 5 minutes. I am by no means a DB2 guru so I do not know how to get that from within it, so I was wondering if there... (4 Replies)
Discussion started by: savigabi
4 Replies

7. Shell Programming and Scripting

Db2 query with script

Hi All, I want to connect two tables in DB2 using shell script and then compare the contents of two tables field by field.and i should return on the screen the un matched records .. Could any one please help me in connecting database tables using Unix and retriving data from the same. (1 Reply)
Discussion started by: kanakaraju
1 Replies

8. Shell Programming and Scripting

Query Oracle tables and return values to shell script that calls the query

Hi, I have a requirement as below which needs to be done viz UNIX shell script (1) I have to connect to an Oracle database (2) Exexute "SELECT field_status from table 1" query on one of the tables. (3) Based on the result that I get from point (2), I have to update another table in the... (6 Replies)
Discussion started by: balaeswari
6 Replies

9. Shell Programming and Scripting

pad db2 query result to 8 bits

Hi , I am using a db2 query to get the maximum sequence number from a field.Now this value is taking up only 2 places.(i.e its less than 100).It can grow up to 3 places later but me requirement is that when i write it in a text file, it should be padded upto 8 places. Please help (1 Reply)
Discussion started by: lifzgud
1 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question