|
Query being run:
Select * from vw_pr01
Left join vw_ins01 on pr01.site_id = vw_ins01.site_id
SHOW CREATE VIEW for vw_pr01:
select `site`.`site_group_id` AS `site_group_id`,`site`.`site_id` AS `site_id`,`site`.`site_alias` AS `site_alias`,`site`.`site_type_id` AS `site_type_id`,`site`.`site_designation_id` AS `site_designation_id`,`site`.`site_classification_id` AS `site_classification_id`,`site`.`site_usage_id` AS `site_usage_id`,`site`.`committee_id` AS `committee_id`,`site`.`ward_id` AS `ward_id`,`site`.`parish_id` AS `parish_id`,`site`.`terrier_status_id` AS `terrier_status_id`,`site`.`listed_type_id` AS `listed_type_id`,`site`.`site_code` AS `site_code`,`site`.`site_desc` AS `site_desc`,`site`.`contact_id` AS `site_contact_id`,`site`.`active` AS `active`,`site`.`asbestos_survey_required` AS `asbestos_survey_required`,`site`.`establishment_id` AS `establishment_id`,`vw_site_address`.`address_id` AS `address_id`,`vw_site_address`.`address_combined` AS `address_combined`,`address`.`second_addr_obj` AS `second_addr_obj`,`address`.`first_addr_obj` AS `first_addr_obj`,`address_street`.`town` AS `town`,`address_street`.`region` AS `region`,`address`.`phone_main` AS `phone_main`,`address`.`second_addr_obj` AS `Sub Dwelling`,`address`.`first_addr_obj` AS `Number/Name`,`address_street`.`street` AS `Street`,`address_street`.`locality` AS `Locality`,`address_street`.`town` AS `Town/City`,`address_street`.`region` AS `County/Region`,`address`.`postcode` AS `Postcode`,`address`.`country` AS `Country`,`address`.`phone_main` AS `Main Phone`,`site`.`site_code` AS `Code`,`site`.`site_desc` AS `Description`,`site`.`site_alias` AS `Site Aliases`,`site`.`active` AS `Site Active`,`site`.`asbestos_survey_required` AS `Asbestos Survey Required`,`site_type`.`site_type_code` AS `Site Type Code`,`site_type`.`site_type_desc` AS `Site Type Desc`,`site_designation`.`site_designation_code` AS `Site Designation Code`,`site_designation`.`site_designation_desc` AS `Site Designation Desc`,`site_classification`.`site_classification_code` AS `Site Classification Code`,`site_classification`.`site_classification_desc` AS `Site Classification Desc`,`site`.`dfe_county_code` AS `LA Code`,`site`.`dfe_number` AS `DFE Number`,`site`.`uprn` AS `UPRN`,`suff_type`.`suff_type_code` AS `Sufficiency Type`,`listed_type`.`listed_type_code` AS `Listed`,`contact`.`contact_name` AS `Contact`,`contact`.`contact_desc` AS `Contact Organisation`,`site`.`opening_hours` AS `Opening Hours`,`gen_gis`.`easting` AS `Easting`,`gen_gis`.`northing` AS `Northing`,`gen_gis`.`gis_ref` AS `GIS Ref`,`prop_tenure`.`prop_tenure_code` AS `Tenure`,`energy_dec_rating`.`energy_dec_rating_code` AS `DEC Rating`,`energy_current_epc_rating`.`energy_epc_rating_code` AS `Current EPC Rating`,`energy_potential_epc_rating`.`energy_epc_rating_code` AS `Potential EPC Rating`,date_format(`site`.`epc_expiry_date`,'%d/%m/%Y') AS `EPC Expiry Date`,`owner_user`.`display_name` AS `Site Owner`,`ward`.`ward_code` AS `Ward Code`,`ward`.`ward_desc` AS `Ward Description`,`parish`.`parish_code` AS `Parish Code`,`parish`.`parish_desc` AS `Parish Description`,`constituency`.`constituency_code` AS `Constituency Code`,`constituency`.`constituency_desc` AS `Constituency Description`,`district`.`district_code` AS `District Code`,`district`.`district_desc` AS `District Description`,`terrier_status`.`terrier_status_code` AS `Terrier Status Code`,`terrier_status`.`terrier_status_desc` AS `Terrier Status Description`,`listed_type`.`listed_type_code` AS `Listed Code`,`listed_type`.`listed_type_desc` AS `Listed Description`,`asset_category`.`asset_category_code` AS `IFRS Category Code`,`asset_category`.`asset_category_desc` AS `IFRS Category Description`,`asset_subcategory`.`asset_subcategory_code` AS `IFRS Sub Category Code`,`asset_subcategory`.`asset_subcategory_desc` AS `IFRS Sub Category Description`,`site_usage`.`site_usage_code` AS `Usage Code`,`site_usage`.`site_usage_desc` AS `Usage Description`,`committee`.`committee_code` AS `Committee Code`,`committee`.`committee_desc` AS `Committee Description`,`site`.`area` AS `Area`,`unit_of_area`.`unit_of_area_code` AS `Unit of area`,`site`.`conservation_area` AS `Conservation Area`,`site`.`ancient_monument` AS `Ancient Monument`,`site`.`area_of_natural_beauty` AS `Area of Natural Beauty`,`site`.`world_heritage` AS `World Heritage`,`site`.`tree_preservation_area` AS `Tree Preservation Area`,`site`.`site_of_special_scientific_interest` AS `Site of Special Scientific Interest`,`site`.`staff_parking` AS `Staff Parking`,`site`.`visitor_parking` AS `Visitor Parking`,`site`.`disabled_parking` AS `Disabled Parking`,`site`.`photo` AS `Photo`,`site`.`location_map` AS `Map Image`,`site`.`vacant` AS `Vacant`,date_format(`site`.`vacant_date`,'%d/%m/%Y') AS `Vacant Date`,`site`.`comment` AS `Comment`,`site`.`account_code_link_mask` AS `Account Code Link`,`gen_userdef_group`.`user_text1` AS `user_text1`,`gen_userdef_group`.`user_text2` AS `user_text2`,`gen_userdef_group`.`user_text3` AS `user_text3`,`gen_userdef_group`.`user_text4` AS `user_text4`,`gen_userdef_group`.`user_text5` AS `user_text5`,`gen_userdef_group`.`user_text6` AS `user_text6`,if(`gen_userdef_group`.`user_check1` = 'Y','Y','N') AS `user_check1`,if(`gen_userdef_group`.`user_check2` = 'Y','Y','N') AS `user_check2`,if(`gen_userdef_group`.`user_check3` = 'Y','Y','N') AS `user_check3`,if(`gen_userdef_group`.`user_check4` = 'Y','Y','N') AS `user_check4`,date_format(`gen_userdef_group`.`user_date1`,'%d/%m/%Y') AS `user_date1`,date_format(`gen_userdef_group`.`user_date2`,'%d/%m/%Y') AS `user_date2`,date_format(`gen_userdef_group`.`user_date3`,'%d/%m/%Y') AS `user_date3`,date_format(`gen_userdef_group`.`user_date4`,'%d/%m/%Y') AS `user_date4`,`gen_userdef_group`.`user_sel1` AS `user_sel1`,`gen_userdef_group`.`user_sel2` AS `user_sel2`,`gen_userdef_group`.`user_sel3` AS `user_sel3`,`gen_userdef_group`.`user_sel4` AS `user_sel4`,`gen_userdef_group`.`sel1_gen_userdef_sel_id` AS `sel1_gen_userdef_sel_id`,`gen_userdef_group`.`sel2_gen_userdef_sel_id` AS `sel2_gen_userdef_sel_id`,`gen_userdef_group`.`sel3_gen_userdef_sel_id` AS `sel3_gen_userdef_sel_id`,`gen_userdef_group`.`sel4_gen_userdef_sel_id` AS `sel4_gen_userdef_sel_id`,`contact1`.`contact_name` AS `user_contact1`,`contact1`.`organisation` AS `user_contact_org1`,`contact2`.`contact_name` AS `user_contact2`,`contact2`.`organisation` AS `user_contact_org2`,`contact3`.`contact_name` AS `user_contact3`,`contact3`.`organisation` AS `user_contact_org3`,`contact4`.`contact_name` AS `user_contact4`,`contact4`.`organisation` AS `user_contact_org4`,`contact5`.`contact_name` AS `user_contact5`,`contact5`.`organisation` AS `user_contact_org5` from (((((((((((((((((((((((((((((((((((`site` left join `unit_of_area` on(`unit_of_area`.`unit_of_area_id` = `site`.`unit_of_area_id`)) left join `suff_type` on(`site`.`suff_type_id` = `suff_type`.`suff_type_id`)) left join `site_type` on(`site`.`site_type_id` = `site_type`.`site_type_id`)) left join `site_designation` on(`site_designation`.`site_designation_id` = `site`.`site_designation_id`)) left join `site_classification` on(`site_classification`.`site_classification_id` = `site`.`site_classification_id`)) left join `contact` on(`site`.`contact_id` = `contact`.`contact_id`)) left join `address` on(`site`.`address_id` = `address`.`address_id`)) left join `address_street` on(`address_street`.`address_street_id` = `address`.`address_street_id`)) left join `gen_gis` on(`site`.`gen_gis_id` = `gen_gis`.`gen_gis_id`)) left join `gen_userdef_group` on(`site`.`gen_userdef_group_id` = `gen_userdef_group`.`gen_userdef_group_id`)) left join `energy_dec_rating` on(`energy_dec_rating`.`energy_dec_rating_id` = `site`.`energy_dec_rating_id`)) left join `ward` on(`site`.`ward_id` = `ward`.`ward_id`)) left join `parish` on(`site`.`parish_id` = `parish`.`parish_id`)) left join `constituency` on(`site`.`constituency_id` = `constituency`.`constituency_id`)) left join `district` on(`site`.`district_id` = `district`.`district_id`)) left join `terrier_status` on(`terrier_status`.`terrier_status_id` = `site`.`terrier_status_id`)) left join `listed_type` on(`listed_type`.`listed_type_id` = `site`.`listed_type_id`)) left join `prop_tenure` on(`prop_tenure`.`prop_tenure_id` = `site`.`prop_tenure_id`)) left join `committee` on(`site`.`committee_id` = `committee`.`committee_id`)) left join `asset_category` on(`site`.`asset_category_id` = `asset_category`.`asset_category_id`)) left join `asset_subcategory` on(`site`.`asset_subcategory_id` = `asset_subcategory`.`asset_subcategory_id`)) left join `site_usage` on(`site`.`site_usage_id` = `site_usage`.`site_usage_id`)) left join `vw_site_address` on(`site`.`site_id` = `vw_site_address`.`site_id`)) left join `user` `owner_user` on(`site`.`fm_user_id` = `owner_user`.`id`)) left join `energy_epc_rating` `energy_current_epc_rating` on(`energy_current_epc_rating`.`energy_epc_rating_id` = `site`.`energy_current_epc_rating_id`)) left join `energy_epc_rating` `energy_potential_epc_rating` on(`energy_potential_epc_rating`.`energy_epc_rating_id` = `site`.`energy_potential_epc_rating_id`)) left join `contact` `contact1` on(`contact1`.`contact_id` = `gen_userdef_group`.`user_contact1`)) left join `contact` `contact2` on(`contact2`.`contact_id` = `gen_userdef_group`.`user_contact2`)) left join `contact` `contact3` on(`contact3`.`contact_id` = `gen_userdef_group`.`user_contact3`)) left join `contact` `contact4` on(`contact4`.`contact_id` = `gen_userdef_group`.`user_contact4`)) left join `contact` `contact5` on(`contact5`.`contact_id` = `gen_userdef_group`.`user_contact5`)) left join `gen_userdef_sel` `gen_userdef_sel1` on(`gen_userdef_sel1`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel1_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel2` on(`gen_userdef_sel2`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel2_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel3` on(`gen_userdef_sel3`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel3_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel4` on(`gen_userdef_sel4`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel4_gen_userdef_sel_id`)) order by `site`.`site_code`
SHOW CREATE VIEW for vw_ins01:
select `site`.`site_group_id` AS `site_group_id`,`site`.`site_id` AS `site_id`,`building`.`building_id` AS `building_id`,`room`.`room_id` AS `room_id`,`plant_group`.`plant_group_id` AS `plant_group_id`,`plant_subgroup`.`plant_subgroup_id` AS `plant_subgroup_id`,`plant_type`.`plant_type_id` AS `plant_type_id`,`plant`.`plant_id` AS `plant_id`,`plant`.`plant_code` AS `plant_code`,`plant_system`.`plant_system_id` AS `plant_system_id`,`inspection`.`inspection_id` AS `inspection_id`,`inspection`.`site_plant_system_id` AS `site_plant_system_id`,`inspection`.`inspection_code` AS `inspection_code`,`inspection`.`inspection_status_id` AS `inspection_status_id`,`inspection_status`.`inspection_status_type_id` AS `inspection_status_type_id`,`inspection`.`owner_user_id` AS `owner_user_id`,`inspection`.`supplier_id` AS `supplier_id`,`inspection_group`.`inspection_group_id` AS `inspection_group_id`,`inspection_type`.`inspection_type_id` AS `inspection_type_id`,`inspection`.`inspection_due_date` AS `inspection_due_date`,`inspection`.`appointment_date` AS `appointment_date`,`inspection`.`inspection_due_week_number` AS `inspection_due_week_number`,`inspection_type`.`perf_in_house` AS `perf_in_house`,`inspection_type`.`statutory` AS `statutory`,`inspection`.`dlo_job_id` AS `dlo_job_id`,`inspection_type`.`float_days` AS `float_days`,`contract`.`contract_id` AS `contract_id`,`inspection`.`contract_invoice_id` AS `contract_invoice_id`,`inspection`.`inspection_code` AS `Inspection Code`,`site`.`site_code` AS `Site Code`,`site`.`site_desc` AS `Site Description`,`parish`.`parish_code` AS `Parish Code`,`parish`.`parish_desc` AS `Parish Description`,`constituency`.`constituency_code` AS `Constituency Code`,`constituency`.`constituency_desc` AS `Constituency Description`,`district`.`district_code` AS `District Code`,`district`.`district_desc` AS `District Description`,`site_type`.`site_type_code` AS `Site Type Code`,`site_type`.`site_type_desc` AS `Site Type Description`,`committee`.`committee_code` AS `Site Committee Code`,`committee`.`committee_desc` AS `Site Committee Description`,`building`.`building_code` AS `Building Code`,`building`.`building_desc` AS `Building Description`,`room`.`room_number` AS `Room No`,`room`.`room_desc` AS `Room Description`,`plant_group`.`plant_group_code` AS `Plant Group`,`plant_group`.`plant_group_desc` AS `Plant Group Description`,`plant_subgroup`.`plant_subgroup_code` AS `Plant Sub Group`,`plant_subgroup`.`plant_subgroup_desc` AS `Plant Sub Group Description`,`plant_type`.`plant_type_code` AS `Plant Type`,`plant_type`.`plant_type_desc` AS `Plant Type Description`,`plant`.`plant_code` AS `Plant Code`,`plant`.`plant_desc` AS `Plant Description`,`plant_system`.`plant_system_code` AS `Plant System Type Code`,`plant_system`.`plant_system_desc` AS `Plant System Type Description`,`site_plant_system`.`site_plant_system_code` AS `Plant System Code`,`site_plant_system`.`site_plant_system_desc` AS `Plant System Description`,`inspection`.`comment` AS `Comment`,`inspection_status`.`code` AS `Status`,`inspection_status_type`.`inspection_status_type_code` AS `Status Type Code`,`inspection_group`.`inspection_group_code` AS `Group`,`inspection_group`.`inspection_group_desc` AS `Group Description`,`inspection_type`.`inspection_type_code` AS `Type`,`inspection_type`.`inspection_type_description` AS `Type Description`,`inspection_type`.`maint_activity` AS `Maintenance Activity`,`inspection_type`.`legislation` AS `Legislation`,if(`inspection_type`.`qual_supplier_req` = 1,'Yes','') AS `Qual. Required`,`inspection_type`.`qualification` AS `Qualifications or Trade Body`,date_format(`inspection`.`inspection_due_date`,'%d/%m/%Y') AS `Due Date`,`inspection`.`inspection_due_week_number` AS `Due Week Number`,`user`.`display_name` AS `Owner`,`contact`.`contact_name` AS `Supplier Contact`,`contact`.`organisation` AS `Supplier Org.`,date_format(`inspection`.`appointment_date`,'%d/%m/%Y') AS `Appointment Date`,replace(format(`inspection`.`labour`,2),',','') AS `Inspection Labour`,replace(format(`inspection`.`material`,2),',','') AS `Inspection Material`,replace(format(`inspection`.`other`,2),',','') AS `Inspection Other`,replace(format(`inspection`.`completed_late_days`,2),',','') AS `Completed Late Days`,`fin_account`.`fin_account_code` AS `Account Code`,`contract`.`contract_code` AS `Contract Code`,`contract`.`contract_desc` AS `Contract Desc`,`contract_invoice`.`contract_invoice_no` AS `Contract Invoice No.`,date_format(`inspection`.`completed_date`,'%d/%m/%Y') AS `Completed Date`,if(`inspection_type`.`logbook_update_req` = 1,'Yes','') AS `Logbook Update Required`,if(`inspection`.`logbook_updated` = 1,'Yes','') AS `Logbook Updated`,if(`inspection_type`.`cert_or_rep_req` = 1,'Yes','') AS `Certificate or Report Required`,if(`inspection`.`certificate_loaded` = 1,'Yes','') AS `Certificate Loaded`,`gen_userdef_group`.`user_text1` AS `user_text1`,`gen_userdef_group`.`user_text2` AS `user_text2`,`gen_userdef_group`.`user_text3` AS `user_text3`,`gen_userdef_group`.`user_text4` AS `user_text4`,`gen_userdef_group`.`user_text5` AS `user_text5`,`gen_userdef_group`.`user_text6` AS `user_text6`,if(`gen_userdef_group`.`user_check1` = 'Y','Y','N') AS `user_check1`,if(`gen_userdef_group`.`user_check2` = 'Y','Y','N') AS `user_check2`,if(`gen_userdef_group`.`user_check3` = 'Y','Y','N') AS `user_check3`,if(`gen_userdef_group`.`user_check4` = 'Y','Y','N') AS `user_check4`,date_format(`gen_userdef_group`.`user_date1`,'%d/%m/%Y') AS `user_date1`,date_format(`gen_userdef_group`.`user_date2`,'%d/%m/%Y') AS `user_date2`,date_format(`gen_userdef_group`.`user_date3`,'%d/%m/%Y') AS `user_date3`,date_format(`gen_userdef_group`.`user_date4`,'%d/%m/%Y') AS `user_date4`,`gen_userdef_group`.`user_sel1` AS `user_sel1`,`gen_userdef_group`.`user_sel2` AS `user_sel2`,`gen_userdef_group`.`user_sel3` AS `user_sel3`,`gen_userdef_group`.`user_sel4` AS `user_sel4`,`gen_userdef_group`.`sel1_gen_userdef_sel_id` AS `sel1_gen_userdef_sel_id`,`gen_userdef_group`.`sel2_gen_userdef_sel_id` AS `sel2_gen_userdef_sel_id`,`gen_userdef_group`.`sel3_gen_userdef_sel_id` AS `sel3_gen_userdef_sel_id`,`gen_userdef_group`.`sel4_gen_userdef_sel_id` AS `sel4_gen_userdef_sel_id`,`contact1`.`contact_name` AS `user_contact1`,`contact1`.`organisation` AS `user_contact_org1`,`contact2`.`contact_name` AS `user_contact2`,`contact2`.`organisation` AS `user_contact_org2`,`contact3`.`contact_name` AS `user_contact3`,`contact3`.`organisation` AS `user_contact_org3`,`contact4`.`contact_name` AS `user_contact4`,`contact4`.`organisation` AS `user_contact_org4`,`contact5`.`contact_name` AS `user_contact5`,`contact5`.`organisation` AS `user_contact_org5` from ((((((((((((((((((((((((((((((((((`inspection` left join `building` on(`inspection`.`building_id` = `building`.`building_id`)) left join `site` on(`inspection`.`site_id` = `site`.`site_id`)) left join `parish` on(`parish`.`parish_id` = `site`.`parish_id`)) left join `constituency` on(`constituency`.`constituency_id` = `site`.`constituency_id`)) left join `district` on(`district`.`district_id` = `site`.`district_id`)) left join `room` on(`inspection`.`room_id` = `room`.`room_id`)) left join `site_type` on(`site`.`site_type_id` = `site_type`.`site_type_id`)) left join `committee` on(`site`.`committee_id` = `committee`.`committee_id`)) left join `inspection_status` on(`inspection`.`inspection_status_id` = `inspection_status`.`inspection_status_id`)) left join `inspection_status_type` on(`inspection_status`.`inspection_status_type_id` = `inspection_status_type`.`inspection_status_type_id`)) left join `inspection_type` on(`inspection`.`inspection_type_id` = `inspection_type`.`inspection_type_id`)) left join `inspection_group` on(`inspection_type`.`inspection_group_id` = `inspection_group`.`inspection_group_id`)) left join `user` on(`inspection`.`owner_user_id` = `user`.`id`)) left join `contact` on(`inspection`.`supplier_id` = `contact`.`contact_id`)) left join `fin_account` on(`fin_account`.`fin_account_id` = `inspection`.`fin_account_id`)) left join `contract_inspection` on(`contract_inspection`.`inspection_id` = `inspection`.`inspection_id`)) left join `contract` on(`contract`.`contract_id` = `contract_inspection`.`contract_id`)) left join `gen_userdef_group` on(`inspection`.`gen_userdef_group_id` = `gen_userdef_group`.`gen_userdef_group_id`)) left join `contract_invoice` on(`contract_invoice`.`contract_invoice_id` = `inspection`.`contract_invoice_id`)) left join `plant_group` on(`inspection`.`plant_group_id` = `plant_group`.`plant_group_id`)) left join `plant_subgroup` on(`inspection`.`plant_subgroup_id` = `plant_subgroup`.`plant_subgroup_id`)) left join `plant_type` on(`inspection`.`plant_type_id` = `plant_type`.`plant_type_id`)) left join `plant` on(`inspection`.`plant_id` = `plant`.`plant_id`)) left join `site_plant_system` on(`inspection`.`site_plant_system_id` = `site_plant_system`.`site_plant_system_id`)) left join `plant_system` on(`site_plant_system`.`plant_system_id` = `plant_system`.`plant_system_id`)) left join `contact` `contact1` on(`contact1`.`contact_id` = `gen_userdef_group`.`user_contact1`)) left join `contact` `contact2` on(`contact2`.`contact_id` = `gen_userdef_group`.`user_contact2`)) left join `contact` `contact3` on(`contact3`.`contact_id` = `gen_userdef_group`.`user_contact3`)) left join `contact` `contact4` on(`contact4`.`contact_id` = `gen_userdef_group`.`user_contact4`)) left join `contact` `contact5` on(`contact5`.`contact_id` = `gen_userdef_group`.`user_contact5`)) left join `gen_userdef_sel` `gen_userdef_sel1` on(`gen_userdef_sel1`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel1_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel2` on(`gen_userdef_sel2`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel2_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel3` on(`gen_userdef_sel3`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel3_gen_userdef_sel_id`)) left join `gen_userdef_sel` `gen_userdef_sel4` on(`gen_userdef_sel4`.`gen_userdef_sel_id` = `gen_userdef_group`.`sel4_gen_userdef_sel_id`)) order by `site`.`site_code`,`building`.`building_code`,`inspection_group`.`inspection_group_code`,`inspection_type`.`inspection_type_code`,`inspection`.`inspection_code`
|
MariaBD_Error.txt