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
------------------------------