select c.crash_id, c.crash_report_number, c.vehicle_id, c.agency_code, (select ac.agency_desc from agency_codes ac where ac.agency_code=c.agency_code) as agency_desc, c.officer_id, get_off_emp_name(c.officer_id,'O') as officer_desc, c.employee_id, get_off_emp_name(c.employee_id,'E') as employee_desc, c.crash_date, c.hour_group_code, (select ec.description from ejs_codes ec where ec.code_type=c.hour_group_code_type and ec.code=c.hour_group_code) as hour_group_desc, c.county_code, (select ec.description from ejs_codes ec where ec.code_type=c.county_code_type and ec.code=c.county_code) as county_code_desc, c.status_code, (select ec.description from ejs_codes ec where ec.code_type=c.status_code_type and ec.code=c.status_code) as status_code_desc, c.crash_type_code, (select ec.description from ejs_codes ec where ec.code_type=c.crash_type_code_type and ec.code=c.crash_type_code) as crash_type_desc, c.crash_cause_code, (select ec.description from ejs_codes ec where ec.code_type=c.crash_cause_code_type and ec.code=c.crash_cause_code) as crash_cause_desc, c.address_id, ejs_format_address(c.address_id) as address_desc, c.location_comment, c.summary, c.is_preventable, c.is_pursuit, c.is_traffic_stop, dv.unit_number, cserv.service_id, cserv.mileage, cserv.start_date, cserv.end_date, cserv.completion_date, nvl(cserv.vendor,iv.vendor_name), cserv.technician, cserv.invoice_number, cserv.hours_down, cserv.comments, cserv.service_cost, cserv.estimated_cost, cserv.supplemental_cost, cserv.creator_id, cserv.creator_date, cserv.updator_id, cserv.updator_date from dv_crashes c inner join department_vehicles dv on c.vehicle_id = dv.vehicle_id inner join dv_service_records cserv on c.crash_id = cserv.crash_id and cserv.crash_id is not null left outer join item_vendors iv on cserv.vendor_id = iv.vendor_id