Topic
6 replies Latest Post - ‏2013-01-02T16:51:28Z by Toxic5
Toxic5
Toxic5
18 Posts
ACCEPTED ANSWER

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
    196 Posts
    ACCEPTED ANSWER

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

    ‏2013-01-02T11:05:37Z  in response to Toxic5
    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
      196 Posts
      ACCEPTED ANSWER

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

      ‏2013-01-02T11:12:20Z  in response to P.Heath
      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
        196 Posts
        ACCEPTED ANSWER

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

        ‏2013-01-02T11:20:29Z  in response to P.Heath
        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
          ACCEPTED ANSWER

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

          ‏2013-01-02T11:46:46Z  in response to P.Heath
          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
            79 Posts
            ACCEPTED ANSWER

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

            ‏2013-01-02T16:33:49Z  in response to Toxic5
            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
              ACCEPTED ANSWER

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

              ‏2013-01-02T16:51:28Z  in response to Bowser
              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.