Confirm

Expand all | Collapse all

SQL to show Job number (Or lack of) on defect Data Source/Report

  • 1.  SQL to show Job number (Or lack of) on defect Data Source/Report

    Posted 12-03-2020 06:22

    Hi, 

    I was wondering if it is at all possible for a data source to be ran that would show all the defects for i.e. Trees but then if a Job had been raised show the job number next to the Defect Number; and if a job hadn't been raised just show a blank field without limiting the defects that show up.

    Then when you drill down on a defect that had been turned into a job - it would offer to take you to the defect and/or job and when you drill down on a defect without a job it would just offer to take you to the defect.

    For example:

    SELECT
    defect.defect_number,
    defect.defect_description,
    central_site.site_code,
    central_site.site_name,
    defect.defect_date,
    defect.defect_type,
    priority.priority_code,
    priority.priority_name
    FROM
    central_site,
    defect,
    priority,
    feature,
    feature_type
    WHERE
    feature_type.feature_type_code = feature.feature_type_code AND
    feature.site_code = defect.site_code AND
    feature.plot_number = defect.plot_number AND
    central_site.site_code = defect.site_code AND
    priority.priority_code = defect.priority_code AND
    priority.priority_code IN ('TRU','TRNR','TRE','TR3M','5TR','6TR','7TR','8TR','9TR','10TR') AND
    defect.defect_status_flag = 'N' AND
    feature.feature_type_code IN ('TREE','GTRE','TRH')

    This shows the defect number for the defect raised with stated priority codes/in stated feature types. However, I am unsure of what the link is that connects the defect.defect_number with the job.job_number.

    Any help in this regard would be greatly appreciated.



    ------------------------------
    Louis Johnston
    Hull City County Council
    HULL
    ------------------------------


  • 2.  RE: SQL to show Job number (Or lack of) on defect Data Source/Report

    Posted 12-03-2020 06:31
    Edited by John Goodlass 12-03-2020 06:32
    Hello Louis

    the link between the job and the defect is

    JOB.JOB_NUMBER=DEFECT.JOB_NUMBER

    ------------------------------
    John Goodlass
    Assistant Engineer  (Systems)
    Street Works and Systems Team
    East Riding Of Yorkshire Council
    East Ridings
    ------------------------------



  • 3.  RE: SQL to show Job number (Or lack of) on defect Data Source/Report

    Posted 12-03-2020 06:41
    Honestly I didn't even try this which is slightly embarrassing this does work effectively. Thank you for your help.

    ------------------------------
    Louis Johnston
    Hull City County Council
    HULL
    ------------------------------



  • 4.  RE: SQL to show Job number (Or lack of) on defect Data Source/Report

    Posted 12-03-2020 06:33
    Hi Louis,

    If you add the field defect.job_number to your SQL, you can then add the Defect and Job tables as "Linked Tables" on the Data Source screen.

    This will enable the drill-down functionality you require.

    Hope this helps.

    Kind regards,
    Steve

    ------------------------------
    Steve Bish
    Senior Software Support Analyst
    Confirm
    ------------------------------



  • 5.  RE: SQL to show Job number (Or lack of) on defect Data Source/Report

    Posted 12-03-2020 06:42

    Hi,

    I have also just tried this and it works effectively. Thank you for the help.



    ------------------------------
    Louis Johnston
    Hull City County Council
    HULL
    ------------------------------