dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 16958
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
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, (