------------------------------------------------------------ revno: 17903 committer: Lars Helge Overland <larshe...@gmail.com> branch nick: dhis2 timestamp: Wed 2015-01-07 13:14:33 +0100 message: Script modified: resources/sql/div.sql resources/sql/integritychecks.sql
-- lp:dhis2 https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk Your team DHIS 2 developers is subscribed to branch lp:dhis2. To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription
=== modified file 'resources/sql/div.sql' --- resources/sql/div.sql 2015-01-05 18:22:31 +0000 +++ resources/sql/div.sql 2015-01-07 12:14:33 +0000 @@ -211,6 +211,15 @@ inner join categoryoptioncombo coc on (dv.categoryoptioncomboid=coc.categoryoptioncomboid) inner join categoryoptioncombo aoc on (dv.attributeoptioncomboid=aoc.categoryoptioncomboid) limit 10000; + +-- Display validation rules which includes the given data element uid + +select distinct vr.uid, vr.name +from validationrule vr +inner join expression le on vr.leftexpressionid=le.expressionid +inner join expression re on vr.rightexpressionid=re.expressionid +where le.expression ~ 'OuudMtJsh2z' +or re.expression ~ 'OuudMtJsh2z' -- (Write) Delete all data values for category combo === modified file 'resources/sql/integritychecks.sql' --- resources/sql/integritychecks.sql 2014-12-05 10:06:08 +0000 +++ resources/sql/integritychecks.sql 2015-01-07 12:14:33 +0000 @@ -105,6 +105,20 @@ create index aggregatedindicatorvalue_index on aggregatedindicatorvalue (indicatorid, periodid, organisationunitid, value); create index aggregateddatasetcompleteness_index on aggregateddatasetcompleteness (datasetid, periodid, organisationunitid, value); +-- Get category option combos from data values which are not part of the category combo of the data element + +select distinct de.name as data_element, dv.dataelementid, de_cc.name as data_element_category_combo, oc_cc.name as option_combo_category_combo, con.categoryoptioncomboname, dv.categoryoptioncomboid +from datavalue dv +left join dataelement de on dv.dataelementid=de.dataelementid +left join categorycombo de_cc on de.categorycomboid=de_cc.categorycomboid +inner join categorycombos_optioncombos cc_oc on dv.categoryoptioncomboid=cc_oc.categoryoptioncomboid +left join categorycombo oc_cc on cc_oc.categorycomboid=oc_cc.categorycomboid +left join _categoryoptioncomboname con on dv.categoryoptioncomboid=con.categoryoptioncomboid +where not exists ( + select 1 from _dataelementcategoryoptioncombo dc + where dc.dataelementid=dv.dataelementid + and dc.categoryoptioncomboid=dv.categoryoptioncomboid); + -- Get category option combos without category options select * from categoryoptioncombo where categoryoptioncomboid not in (select distinct categoryoptioncomboid from categoryoptioncombos_categoryoptions);
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : dhis2-devs@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
