Actions
task #9501
openFind inconsistent higher area distribution status data in E+M
Start date:
Due date:
% Done:
0%
Estimated time:
Severity:
normal
Description
The distribution status of a higher area (area with subareas) should usually not have a higher status then the highest status of it's subareas if subarea information exists.
This can be found by the following sql
SELECT n.titleCache nameTitle, n.nameCache, a.titleCache area, a.idInVocabulary areaId, st.titleCache statusTitle, sec.titleCache sec FROM DescriptionElementBase deb INNER JOIN DescriptionBase db ON db.id = deb.inDescription_id INNER JOIN TaxonBase tb ON tb.id = db.taxon_id INNER JOIN TaxonName n ON n.id = tb.name_id INNER JOIN DefinedTermBase a ON a.id = deb.area_id INNER JOIN DefinedTermBase st ON st.id = deb.status_id INNER JOIN Reference sec ON sec.id = tb.sec_id WHERE tb.publish = 1 AND deb.DTYPE = 'Distribution' AND EXISTS ( SELECT * FROM DescriptionElementBase deb2 INNER JOIN DescriptionBase db2 ON db2.id = deb2.inDescription_id INNER JOIN DefinedTermBase a2 ON a2.id = deb2.area_id LEFT JOIN DefinedTermBase st2 ON st2.id = deb2.status_id WHERE a.id = a2.partOf_id AND tb.id = db2.taxon_id AND st.orderIndex < st2.orderIndex AND st2.id <> 2409 ) AND NOT EXISTS ( SELECT * FROM DescriptionElementBase deb3 INNER JOIN DescriptionBase db3 ON db3.id = deb3.inDescription_id INNER JOIN DefinedTermBase a3 ON a3.id = deb3.area_id LEFT JOIN DefinedTermBase st3 ON st3.id = deb3.status_id WHERE a.id = a3.partOf_id AND tb.id = db3.taxon_id AND st.orderIndex >= st3.orderIndex ) AND a.id <> 2269 -- EM area ORDER BY n.titleCache, a.titleCache
For areas with status "undefined" change to "st2.id = 2409"
===
other data cleaning issues concerning status:
- undefined: #4822#note-22
Related issues
Actions