Confirm

Expand all | Collapse all

Action Officer Areas

Jump to Best Answer
  • 1.  Action Officer Areas

    Posted 09-09-2019 06:25
    Hi All

    I am trying to write some SQL that identifies which Area an Action Officer is assigned to but when I run the SQL below all I get is a list which shows the Areas and officer Name multiple times? its as if it is showing multiple Subject Sites but all I need is the Area a specific Action Officer is assigned to not all the Subject Sites.

    Any ideas?

    SELECT
    area.area_code,
    area.area_name,
    action_officer.officer_name,
    action_officer.work_group_code
    FROM
    action_officer,
    area,
    central_site,
    subject_site
    WHERE
    action_officer.work_group_code = 'EFPL' AND
    area.area_code = central_site.area_code AND
    central_site.site_code = subject_site.site_code AND
    action_officer.officer_code = subject_site.officer_code

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------


  • 2.  RE: Action Officer Areas
    Best Answer

    Posted 09-09-2019 13:51
    Hi Martin,

    If you use a DISTINCT or filter to a specific service & Subject, it should remove the duplicate officers.

    Alternatively, if you have a route setup, and have assigned to an officer, this should give you the area name, therefore no longer needing the enquiry subject site table in your query.

    Regards

    Haf

    ------------------------------
    Hafezur Rahman
    Confirm Systems Administrator
    London Borough Of Richmond Upon Thames
    Twickenham
    ------------------------------



  • 3.  RE: Action Officer Areas

    Posted 09-10-2019 08:13
    Thanks Haf

    Added a DISTINCT and now get the result i wanted.

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------



  • 4.  RE: Action Officer Areas

    Pitney Bowes
    Posted 09-10-2019 03:13
    Edited by Christopher Wareham 09-10-2019 03:14
    Hi Martin

    The reason you are getting duplicates is because there can be multiple rows in the subject_site table for the same site_code/officer_code combination.  The primary key for the subject_site table is service_code/subject_code/site_code which you can see in the help files under Reporting > Schema Diagrams > Customer Services - Enquiries then look at the subject_site table.  The schema diagrams are also in the installation package that on-premise clients download and are in the \Issue\Documents\Manuals\confirm-vXX-XX-reporting-user-manual.pdf file.

    Haf says 'use a DISTINCT or filter to a specific service & Subject'.  By using a filter you restrict the SQL to the subject_site table primary key.

    ------------------------------
    Chris Wareham
    Senior Technical Support Analyst
    Pitney Bowes
    ------------------------------



  • 5.  RE: Action Officer Areas

    Posted 09-10-2019 08:13
    Hi Chris

    Added a DISTINCT as suggested by Haf and now get the result I wanted.

    ------------------------------
    Martin Langler
    EAST RIDING OF YORKSHIRE COUNCIL
    BEVERLEY
    ------------------------------