IC5Notice: We have upgraded developerWorks Community to the latest version of IBM Connections. For more information, read our upgrade FAQ.
Topic
  • 6 replies
  • Latest Post - ‏2013-01-02T16:51:28Z by Toxic5
Toxic5
Toxic5
18 Posts

Pinned topic SQL to find persons down hierarchy by Supervisor? PLEASE!

‏2013-01-02T10:31:32Z |
Hi, I will try to explain simply what I want!;

I have 4 test users, each reports to next: Senior Mngr / Area Mngr / Supervisor / Technician , each has their line manager as PERSON.SUPERVISOR

I want a SQL that returns all PO's in PO APP when the logged on user is 'Senior Mngr' and the PO raiser is anyone beneath him (ie: Area mngr, Supervisor OR Technician).

I already have the following SQL which works BUT only brings back for ONE level BELOW logged on user (ie if Senior Mngr logged on then he only sees PO's raised by Area Mngr):
ponum in (Select distinct ponum from poline where Requestedby in (Select personid from person where supervisor in (Select personid from maxuser where userid = :USER))) and historyflag = 0 and siteid in ('GFL','NEN') and receipts in ('PARTIAL','NONE')
How can I modify to bring back ALL PO's raised by the cascading three juniors when logged on user is Senior Mngr?

Any help greatly appreciated, regards, Jon.
Updated on 2013-01-02T16:51:28Z at 2013-01-02T16:51:28Z by Toxic5
  • P.Heath
    P.Heath
    199 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T11:05:37Z  
    Replace:

    Requestedby in (Select personid from person where supervisor in (Select personid from maxuser where userid = :USER))

    With:

    Requestedby in (Select personid from person where supervisor=:user OR supervisor in (Select personid from person where supervisor=:user) OR supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:user))) OR supervisor in (Select personid from person where (supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:user)))))
    )

    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
  • P.Heath
    P.Heath
    199 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T11:12:20Z  
    • P.Heath
    • ‏2013-01-02T11:05:37Z
    Replace:

    Requestedby in (Select personid from person where supervisor in (Select personid from maxuser where userid = :USER))

    With:

    Requestedby in (Select personid from person where supervisor=:user OR supervisor in (Select personid from person where supervisor=:user) OR supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:user))) OR supervisor in (Select personid from person where (supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:user)))))
    )

    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
    Correction
    Replace with:
    Requestedby in (Select personid from person where supervisor=:USER)
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where supervisor=:USER))
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER))))
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER))))))

    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
  • P.Heath
    P.Heath
    199 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T11:20:29Z  
    • P.Heath
    • ‏2013-01-02T11:12:20Z
    Correction
    Replace with:
    Requestedby in (Select personid from person where supervisor=:USER)
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where supervisor=:USER))
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER))))
    OR
    Requestedby in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER))))))

    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
    Shortened:

    Requestedby in (Select personid from person where supervisor=:USER OR supervisor in (Select personid from person where supervisor=:USER) OR supervisor in (Select personid from person where supervisor in (Select personid from person where supervisor=:USER)) OR supervisor in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER)))))
    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
  • Toxic5
    Toxic5
    18 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T11:46:46Z  
    • P.Heath
    • ‏2013-01-02T11:20:29Z
    Shortened:

    Requestedby in (Select personid from person where supervisor=:USER OR supervisor in (Select personid from person where supervisor=:USER) OR supervisor in (Select personid from person where supervisor in (Select personid from person where supervisor=:USER)) OR supervisor in (Select personid from person where supervisor in (Select personid from person where (supervisor in (Select personid from person where supervisor=:USER)))))
    Best regards,
    Paul Heath Armengol

    Methodology Consultant & Maximo/Rational Tools Administrator
    COSTAISA S.A.
    Barcelona, Spain
    Thank you Paul, that was a great help, have modified SQL with third shortened option and got back all my test PO's for test users, regards, Jon.
  • Bowser
    Bowser
    81 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T16:33:49Z  
    • Toxic5
    • ‏2013-01-02T11:46:46Z
    Thank you Paul, that was a great help, have modified SQL with third shortened option and got back all my test PO's for test users, regards, Jon.
    Toxic5, you could investigate using a hierarchical or recursive query if you are using Oracle or DB2, respectively. This would allow your query to cope with any number of "supervisor levels", rather than being limited to 4 levels.
  • Toxic5
    Toxic5
    18 Posts

    Re: SQL to find persons down hierarchy by Supervisor? PLEASE!

    ‏2013-01-02T16:51:28Z  
    • Bowser
    • ‏2013-01-02T16:33:49Z
    Toxic5, you could investigate using a hierarchical or recursive query if you are using Oracle or DB2, respectively. This would allow your query to cope with any number of "supervisor levels", rather than being limited to 4 levels.
    Thanks for taking time to reply, even though now answered. Our MXO sits on a Microsoft SQL DB, as such assume your suggestion would not apply? Otherwise I would have given that a go too in our UAT system, thanks again, Jon.