Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2015
    Posts
    6

    Unanswered: Help with Sub Query and where

    Hello All

    We have a below table structure

    Emplid , Level , Level1superviourID, Level2superviourID, Level3superviourID , Level4superviourID

    Data Sample :

    emplid level Level1superviourID Level2superviourID Level3superviourID Level3superviourID
    ---------------------------------------------------------------------------------------------------------------------------------------------
    100 1 100
    200 2 100 200
    300 3 100 200 300
    400 4 100 200 300 400

    Requirement :
    ____________

    User will enter Emplid - Based on that we have find the level number

    Example - Select emplid, level from table a where emplid ='user entered value' (200)

    We got the level from above statement

    Next step is get all the employees who reports to that employee ..

    Example elect emplid from table a where Level2superviourID ='user entered value'(200) - Count will be 3 - Because his level is 2 - I have to filter Level2superviourID --

    Help me out with some sample SQL in DB2 ..I really appreciate any help on this

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Did you try to search on Google for DB2 hierarchical query?
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    hi, db2ques
    Maybe you need to alter your table structure to something like this :
    empid level parentid parentlevel
    200 2 100 1
    300 3 200 2
    300 3 100 1
    400 4 300 3
    400 4 200 2
    400 4 100 1
    if you want to get all the employees who reports to employee 200:
    select * from table where parentid =200;

  4. #4
    Join Date
    Sep 2004
    Posts
    15
    Quote Originally Posted by fengsun2 View Post
    hi, db2ques
    Maybe you need to alter your table structure to something like this :
    empid level parentid parentlevel
    v 200 2 100 1
    300 3 200 2
    300 3 100 1
    400 4 300 3
    400 4 200 2
    400 4 100 1
    if you want to get all the employees who reports to employee 200:
    select * from table where parentid =200;
    I agree, your original table design is not right! What if next time your company wants another level of supervisors?

    But, given your problem and data model, lets see.

    emplid level Level1superviourID Level2superviourID Level3superviourID Level3superviourID
    ---------------------------------------------------------------------------------------------------------------------------------------------
    100 1 100
    200 2 100 200
    300 3 100 200 300
    400 4 100 200 300 400

    SELECT DECODE(?, Level1SupervisorID, 1, Level2SupervisorID, 2, Level3SupervisorID, 3, 0) Level FROM YourTable WHERE EPMID = ?

    Here "?" is your empid that the users key in

    Now lets see how to filter all the employees under or above your level

    SELECT * FROM YourTable WHERE level >= DECODE(?, Level1SupervisorID, 1, Level2SupervisorID, 2, Level3SupervisorID, 3, 0);

    You can use "CASE" statement instead of DECODE, but I personally like DECODE over CASE for these simple checks

    Hope this solves your problem.

    But seriously, change the table structure, this is not a good way to do it and using such expression in your "WHERE" clause can have serious impact on the performance if dealing with huge data.

    Cheers,
    Faisal.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •