------------------------------------------------------------ revno: 16958 committer: Lars Helge Overland <larshe...@gmail.com> branch nick: dhis2 timestamp: Sun 2014-10-05 16:09:41 +0200 message: SQL 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 2014-10-04 12:27:48 +0000 +++ resources/sql/div.sql 2014-10-05 14:09:41 +0000 @@ -149,11 +149,12 @@ -- Display overview of data elements and related category option combos -select de.uid as deuid, de.name as dename, coc.uid as cocuid, con.categoryoptioncomboname -from dataelement de -join categorycombos_optioncombos cc using(categorycomboid) -join categoryoptioncombo coc using(categoryoptioncomboid) -join _categoryoptioncomboname con using(categoryoptioncomboid); +select de.uid as dataelement_uid, de.name as dataelement_name, de.code as dataelement_code, coc.uid as optioncombo_uid, cocn.categoryoptioncomboname as optioncombo_name +from _dataelementcategoryoptioncombo dcoc +inner join dataelement de on dcoc.dataelementuid=de.uid +inner join categoryoptioncombo coc on dcoc.categoryoptioncombouid=coc.uid +inner join _categoryoptioncomboname cocn on coc.categoryoptioncomboid=cocn.categoryoptioncomboid +order by de.name; -- Display category option combo identifier and name @@ -162,6 +163,16 @@ join _categoryoptioncomboname cn on (cc.categoryoptioncomboid=cn.categoryoptioncomboid); +-- Display overview of category option combo + +select coc.categoryoptioncomboid as coc_id, coc.uid as coc_uid, co.categoryoptionid as co_id, co.name as co_name, cc.categorycomboid as cc_id, cc.name as cc_name +from categoryoptioncombo coc +inner join categoryoptioncombos_categoryoptions coo on coc.categoryoptioncomboid=coo.categoryoptioncomboid +inner join dataelementcategoryoption co on coo.categoryoptionid=co.categoryoptionid +inner join categorycombos_optioncombos cco on coc.categoryoptioncomboid=cco.categoryoptioncomboid +inner join categorycombo cc on cco.categorycomboid=cc.categorycomboid +where coc.categoryoptioncomboid=2118430; + -- Display data out of reasonable time range select * === modified file 'resources/sql/integritychecks.sql' --- resources/sql/integritychecks.sql 2014-09-30 08:04:49 +0000 +++ resources/sql/integritychecks.sql 2014-10-05 14:09:41 +0000 @@ -133,6 +133,10 @@ select * from categorycombo where categorycomboid not in (select distinct categorycomboid from categorycombos_categories); +-- Get category options with more than one membership for a category + +select categoryid, categoryoptionid, count(*) from categories_categoryoptions group by categoryid, categoryoptionid having count(*) > 1; + -- Get category options with count of memberships in categories select cc.categoryoptionid, co.name, (
_______________________________________________ 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
