NoPublic, bureaucrat, administrator
1,068
edits
Changes
no edit summary
== Examples ==__NOEDITSECTION__
=== View my_view_hazardreductions in Afghanistan ===__NOEDITSECTION__Version with a very poor performance:<pre>CREATE OR REPLACE VIEW my_view_hazardreductions ASSELECT type.enumvalue AS Type,imsmaenum.enumvalue AS Status,hazreduc.hazreduc_guid AS hazreduc_guid,round(hazreduc.areasize,0) AS areasize,hazreduc.enddate AS enddate,areatype.stringvalue AS stringvalue,my_view_gazetteer.Region AS Region,my_view_gazetteer.Province AS Province,my_view_gazetteer.District AS District,hazreduc.destructionmethod AS destructionmethod,areatype.cdf_id AS cdf_id,method_used.enumcategory AS enumcategory,method_used.enumvalue AS method_used,my_view_organization.ShortName AS ShortNameFROM hazreducLEFT JOIN imsmaenum Type ON (type.imsmaenum_guid = hazreduc.hazreductypeenum_guid)LEFT JOIN imsmaenum ON (imsmaenum.imsmaenum_guid = hazreduc.status_guid)JOIN hazreduc_has_cdfvalue ON (hazreduc.hazreduc_guid = hazreduc_has_cdfvalue."HazReduc_GUID")JOIN cdfvalue AreaType ON (hazreduc_has_cdfvalue."CDFValue_GUID" = areatype.cdfvalue_guid)LEFT JOIN location ON (hazreduc.location_guid = location.location_guid)LEFT JOIN my_view_gazetteer ON (location.gazetteer_guid = my_view_gazetteer.gazetteer_guid)LEFT JOIN hazreduc_has_imsmaenum ON (hazreduc.hazreduc_guid = hazreduc_has_imsmaenum.hazreduc_guid)LEFT JOIN imsmaenum method_used ON n (hazreduc_has_imsmaenum.imsmaenum_guid = method_used.imsmaenum_guid)JOIN customdefinedfield ON (customdefinedfield.cdf_id = areatype.cdf_id)LEFT JOIN my_view_organization ON (hazreduc.org_guid = my_view_organization.org_guid)WHERE type.enumvalue = 'Progress Report'AND method_used.enumcategory = 'Clearance Operation'AND customdefinedfield.name = 'CDF-Clearance Type';</pre>Optimised version:<pre>WITH hazreduc_imsmaenum AS (SELECT hazreduc.hazreduc_guid,type.enumvalue AS Type,imsmaenum.enumvalue AS Status,round(hazreduc.areasize,0) AS areasize,hazreduc.enddate AS enddate,hazreduc.destructionmethod AS destructionmethod,hazreduc.org_guid,hazreduc.location_guidLEFT JOIN imsmaenum Type on (type.imsmaenum_guid = hazreduc.hazreductypeenum_guid)LEFT JOIN imsmaenum on (imsmaenum.imsmaenum_guid = hazreduc.status_guid)where type.enumvalue = 'Progress Report'),hazreduc_cdfs AS (SELECT hazreduc.hazreduc_guid,method_used.enumcategory AS enumcategory,method_used.enumvalue AS method_used,areatype.stringvalue AS stringvalue,areatype.cdf_id AS cdf_idFROM hazreducJOIN hazreduc_has_cdfvalue on (hazreduc.hazreduc_guid = hazreduc_has_cdfvalue."HazReduc_GUID")JOIN cdfvalue AreaType on (hazreduc_has_cdfvalue."CDFValue_GUID" = areatype.cdfvalue_guid)JOIN customdefinedfield on (areatype.cdf_id = customdefinedfield.cdf_id)LEFT JOIN hazreduc_has_imsmaenum on (hazreduc.hazreduc_guid = hazreduc_has_imsmaenum.hazreduc_guid)LEFT JOIN imsmaenum method_used on (hazreduc_has_imsmaenum.imsmaenum_guid = method_used.imsmaenum_guid)WHERE method_used.enumcategory = 'Clearance Operation'AND customdefinedfield.name = 'CDF-Clearance Type')selecthazreduc_imsmaenum.Type,hazreduc_imsmaenum.Status,hazreduc_imsmaenum.hazreduc_guid,hazreduc_imsmaenum.areasize,hazreduc_imsmaenum.enddate,hazreduc_cdfs.stringvalue,my_view_gazetteer.Region AS Region,my_view_gazetteer.Province AS Province,my_view_gazetteer.District AS District,hazreduc_imsmaenum.destructionmethod,hazreduc_cdfs.cdf_id,hazreduc_cdfs.enumcategory,hazreduc_cdfs.method_used,my_view_organization.ShortName AS ShortNamefrom hazreduc_imsmaenumjoin my_view_gazetteer on (hazreduc_imsmaenum.location_guid = my_view_gazetteer.location_guid)join hazreduc_cdfs on (hazreduc_imsmaenum.hazreduc_guid = hazreduc_cdfs.hazreduc_guid)left join my_view_organization on (hazreduc_imsmaenum.org_guid = my_view_organization.org_guid);</pre>
{{NavBox Hub}}
[[Category:NoPublic]]
[[Category:VIE]]