How to use SQL to display Inspection Route data in Confirm

UPDATED: August 3, 2017

This is SQL similar to that used by Confirm® to display the actual data in the Inspection Route screen. Here we can see from which tables and columns data is read by the system for its render and display in the Inspection Route screen. Of particular note, the last Inspection Date is obtained by the SELECT subquery which also joins back to the outer query,

SELECT insp_route_feat.insp_route_code, insp_route_feat.site_code, insp_route_feat.plot_number, insp_route_feat.insp_route_order, insp_route_feat.work_group_code, insp_route_feat.complete_flag, insp_route_feat.officer_code, insp_route_feat.start_chainage, insp_route_feat.end_chainage, insp_route_feat.start_ref_label, insp_route_feat.xsp_code, insp_route_feat.route_feat_notes, insp_route_feat.end_ref_label, central_site.site_name, feature.feature_location, feature.feature_id, feat_measurement.feature_quantity, measurement_type.unit_code, feature_type.feature_type_name, action_officer.officer_name, locality.locality_name, town.town_name, county.county_name, insp_route_feat.insp_due_date,     (SELECT Max( feature_insp_date) 
     FROM inspection_feature, inspection_batch
     WHERE inspection_feature.insp_batch_no = inspection_batch.insp_batch_no
       AND inspection_batch.insp_route_code = inspection_route.insp_route_code
       AND inspection_batch.workgroup_code = inspection_route.work_group_code
       and inspection_feature.site_code = insp_route_feat.site_code
       and inspection_feature.plot_number = insp_route_feat.plot_number) as last_insp_date
FROM insp_route_feat LEFT OUTER JOIN action_officer ON insp_route_feat.officer_code = action_officer.officer_code, central_site, feature, feat_measurement, measurement_type, feature_type, feature_group, inspection_route, locality, town, county
WHERE inspection_route.insp_route_code = 'AVDF'
    AND inspection_route.work_group_code = 'PT'
    AND ( feature.site_code = insp_route_feat.site_code )
    and ( feature.plot_number = insp_route_feat.plot_number )
    and ( feature.site_code = feat_measurement.site_code )
    and ( feature.plot_number = feat_measurement.plot_number )
    and ( measurement_type.measurement_code = feat_measurement.measurement_code )
    and ( feature.site_code = central_site.site_code )
    and ( feature_type.feature_type_code = feature.feature_type_code )
    and ( feature_group.feature_group_code = feature_type.feature_group_code )
    and ( measurement_type.measurement_code = feature_group.measurement_code )
    and ( inspection_route.insp_route_code = insp_route_feat.insp_route_code )
    and ( inspection_route.work_group_code = insp_route_feat.work_group_code )
    and ( locality.town_id = town.town_id )
    and ( town.county_id = county.county_id )
    and ( central_site.locality_id = locality.locality_id );

This query is applicable to both Oracle and SQL Server. You will need to replace the Inspection Route code and Work Group code with your own.

Environment Details

Product Feature: Ad-Hoc Reporting


  • No Downloads