=== modified file 'dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java'
--- dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java	2013-05-24 04:19:07 +0000
+++ dhis-2/dhis-services/dhis-service-patient/src/main/java/org/hisp/dhis/program/hibernate/HibernateProgramStageInstanceStore.java	2013-05-24 05:22:57 +0000
@@ -1161,7 +1161,7 @@
 
                 if ( orgunitIds.size() == 0 )
                 {
-                    sql += "(SELECT ( cast( \'0\' as " + statementBuilder.getDoubleColumnType() + " )) ";
+                    sql += "(SELECT 0  ";
                 }
                 else
                 {
@@ -1254,7 +1254,7 @@
 
                 if ( orgunitIds.size() == 0 )
                 {
-                    sql += "(SELECT \'0\' ";
+                    sql += "(SELECT 0 ";
                 }
                 else
                 {
@@ -1300,7 +1300,7 @@
         }
 
         sql = sql.substring( 0, sql.length() - 6 ) + " ";
-        sql += " ORDER BY orgunit asc ";
+        sql += " ) ORDER BY orgunit asc ";
         if ( limit != null )
         {
             sql += "LIMIT " + limit;
@@ -1343,7 +1343,7 @@
                 Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
                 if ( orgunitIds.size() == 0 )
                 {
-                    sql += "(SELECT \'0\' ";
+                    sql += "(SELECT 0 ";
                 }
                 else
                 {
@@ -1389,7 +1389,7 @@
             }
         }
 
-        sql = sql.substring( 0, sql.length() - 10 );
+        sql = sql.substring( 0, sql.length() - 10 ) + " ) ";
         if ( limit != null )
         {
             sql += " LIMIT " + limit;
@@ -1420,7 +1420,7 @@
             Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
             if ( orgunitIds.size() == 0 )
             {
-                sql += "(SELECT \'0\' ";
+                sql += "(SELECT 0 ";
             }
             else
             {
@@ -1539,7 +1539,7 @@
                 {
                     for ( String deValue : deValues )
                     {
-                        sql += "(SELECT \'0\' as \"" + deValue + "\",";
+                        sql += "(SELECT 0 as \"" + deValue + "\",";
                     }
                 }
                 else
@@ -1631,7 +1631,7 @@
             Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
             if ( orgunitIds.size() == 0 )
             {
-                sql += "(SELECT \'0\' ";
+                sql += "(SELECT 0 ";
             }
             else
             {
@@ -1699,86 +1699,156 @@
             allOrgunitIds.addAll( getOrganisationUnits( root, facilityLB ) );
         }
 
-        String dataValueSql = "SELECT DISTINCT(pdv.value) ";
-        dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi";
-        dataValueSql += "       ON pdv.programstageinstanceid=psi.programstageinstanceid ";
-        dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " AND ";
-        dataValueSql += "       psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
-            + " ) AND ";
-        dataValueSql += "       psi.programstageid=" + programStage.getId() + " AND ( ";
-
-        dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
-        dataValueSql += "   psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR ";
-
-        dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 );
-        dataValueSql += ") ORDER BY value asc";
-
-        Collection<String> deValues = new HashSet<String>();
-        try
-        {
-            deValues = jdbcTemplate.query( dataValueSql, new RowMapper<String>()
-            {
-                public String mapRow( ResultSet rs, int rowNum )
-                    throws SQLException
-                {
-                    return rs.getString( 1 );
-                }
-            } );
-        }
-        catch ( Exception ex )
-        {
-            ex.printStackTrace();
-        }
-
-        if ( deValues.size() > 0 )
-        {
-            for ( Integer root : roots )
-            {
-                allOrgunitIds = getOrganisationUnits( root, facilityLB );
-                Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
-
+        if ( allOrgunitIds.size() > 0 )
+        {
+            String dataValueSql = "SELECT DISTINCT(pdv.value) ";
+            dataValueSql += "FROM patientdatavalue pdv JOIN programstageinstance psi";
+            dataValueSql += "       ON pdv.programstageinstanceid=psi.programstageinstanceid ";
+            dataValueSql += "WHERE pdv.dataelementid=" + deGroupBy + " AND ";
+            dataValueSql += "       psi.organisationunitid in ( " + TextUtils.getCommaDelimitedString( allOrgunitIds )
+                + " ) AND ";
+            dataValueSql += "       psi.programstageid=" + programStage.getId() + " AND ( ";
+
+            dataValueSql += " ( psi.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+            dataValueSql += "   psi.executiondate <= '" + format.formatDate( period.getEndDate() ) + "') OR ";
+
+            dataValueSql = dataValueSql.substring( 0, dataValueSql.length() - 3 );
+            dataValueSql += ") ORDER BY value asc";
+
+            Collection<String> deValues = new HashSet<String>();
+            try
+            {
+                deValues = jdbcTemplate.query( dataValueSql, new RowMapper<String>()
+                {
+                    public String mapRow( ResultSet rs, int rowNum )
+                        throws SQLException
+                    {
+                        return rs.getString( 1 );
+                    }
+                } );
+            }
+            catch ( Exception ex )
+            {
+                ex.printStackTrace();
+            }
+
+            if ( deValues.size() > 0 )
+            {
+                for ( Integer root : roots )
+                {
+                    allOrgunitIds = getOrganisationUnits( root, facilityLB );
+                    Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+                    sql += "(SELECT ";
+                    sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root
+                        + " ) as orgunit, ";
+                    for ( String deValue : deValues )
+                    {
+                        if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+                        {
+                            sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+                        }
+                        else
+                        {
+                            sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
+                        }
+                        sql += "FROM patientdatavalue pdv_1 ";
+                        sql += "        inner join programstageinstance psi_1 ";
+                        sql += "          on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+                        sql += "WHERE ";
+                        sql += "        psi_1.executiondate >= '" + format.formatDate( period.getStartDate() )
+                            + "' AND ";
+                        sql += "        psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+                        sql += "        psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
+                            + ") AND ";
+                        if ( deSum != null )
+                        {
+                            sql += " dataelementid=" + deSum + " AND ";
+                        }
+                        if ( useCompletedEvents != null )
+                        {
+                            sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+                        }
+                        sql += "        psi_1.programstageid=" + programStage.getId() + " ";
+                        sql += filterSQL + " AND ";
+                        sql += "   (SELECT value FROM patientdatavalue  ";
+                        sql += "   WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
+                        sql += "     dataelementid= pdv_1.dataelementid AND ";
+                        sql += "     dataelementid=" + deGroupBy + "  ) = '" + deValue + "' ";
+                        sql += "   LIMIT 1 ) as \"" + deValue + "\",";
+                    }
+
+                    sql = sql.substring( 0, sql.length() - 1 ) + " ) ";
+                    sql += " UNION ";
+                }
+
+                sql = sql.substring( 0, sql.length() - 6 );
+                if ( limit != null )
+                {
+                    sql += " LIMIT " + limit;
+                }
+            }
+        }
+
+        return sql;
+    }
+
+    /**
+     * Aggregate report Position Orgunit Rows - Period Filter - Data Columns
+     * 
+     **/
+    private String getAggregateReportSQL7WithoutGroup( ProgramStage programStage, Collection<Integer> roots,
+        String facilityLB, String filterSQL, Integer deSum, Period period, String aggregateType, Integer limit,
+        Boolean useCompletedEvents, I18nFormat format )
+    {
+
+        String sql = "";
+
+        for ( Integer root : roots )
+        {
+            Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
+            Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
+
+            if ( orgunitIds.size() > 0 )
+            {
                 sql += "(SELECT ";
                 sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root
                     + " ) as orgunit, ";
-                for ( String deValue : deValues )
-                {
-                    if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
-                    {
-                        sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
-                    }
-                    else
-                    {
-                        sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
-                    }
-                    sql += "FROM patientdatavalue pdv_1 ";
-                    sql += "        inner join programstageinstance psi_1 ";
-                    sql += "          on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
-                    sql += "WHERE ";
-                    sql += "        psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
-                    sql += "        psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
-                    sql += "        psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
-                        + ") AND ";
-                    if ( deSum != null )
-                    {
-                        sql += " dataelementid=" + deSum + " AND ";
-                    }
-                    if ( useCompletedEvents != null )
-                    {
-                        sql += " psi_1.completed = " + useCompletedEvents + " AND ";
-                    }
-                    sql += "        psi_1.programstageid=" + programStage.getId() + " ";
-                    sql += filterSQL + " AND ";
-                    sql += "   (SELECT value FROM patientdatavalue  ";
-                    sql += "   WHERE programstageinstanceid=psi_1.programstageinstanceid AND ";
-                    sql += "     dataelementid= pdv_1.dataelementid AND ";
-                    sql += "     dataelementid=" + deGroupBy + "  ) = '" + deValue + "' ";
-                    sql += "   LIMIT 1 ) as \"" + deValue + "\",";
-                }
-
-                sql = sql.substring( 0, sql.length() - 1 ) + " ) ";
+
+                if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
+                {
+                    sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
+                }
+                else
+                {
+                    sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
+                }
+
+                sql += "FROM patientdatavalue pdv_1 ";
+                sql += "        inner join programstageinstance psi_1 ";
+                sql += "          on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+                sql += "WHERE ";
+                sql += "        psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+                sql += "        psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+                if ( deSum != null )
+                {
+                    sql += " dataelementid=" + deSum + " AND ";
+                }
+                if ( useCompletedEvents != null )
+                {
+                    sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+                }
+                sql += "        psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds )
+                    + ") AND ";
+                sql += "        psi_1.programstageid=" + programStage.getId() + " ";
+                sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\" ) ";
+
                 sql += " UNION ";
             }
+        }
 
+        if ( !sql.isEmpty() )
+        {
             sql = sql.substring( 0, sql.length() - 6 );
             if ( limit != null )
             {
@@ -1790,64 +1860,6 @@
     }
 
     /**
-     * Aggregate report Position Orgunit Rows - Period Filter - Data Columns
-     * 
-     **/
-    private String getAggregateReportSQL7WithoutGroup( ProgramStage programStage, Collection<Integer> roots,
-        String facilityLB, String filterSQL, Integer deSum, Period period, String aggregateType, Integer limit,
-        Boolean useCompletedEvents, I18nFormat format )
-    {
-
-        String sql = "";
-
-        for ( Integer root : roots )
-        {
-            Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
-            Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
-
-            sql += "(SELECT ";
-            sql += "( SELECT ou.name FROM organisationunit ou WHERE ou.organisationunitid=" + root + " ) as orgunit, ";
-
-            if ( aggregateType.equals( PatientAggregateReport.AGGREGATE_TYPE_COUNT ) )
-            {
-                sql += "(SELECT count(DISTINCT psi_1.programstageinstanceid) ";
-            }
-            else
-            {
-                sql += "(SELECT " + aggregateType + "( cast( value as DOUBLE PRECISION )) ";
-            }
-
-            sql += "FROM patientdatavalue pdv_1 ";
-            sql += "        inner join programstageinstance psi_1 ";
-            sql += "          on psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
-            sql += "WHERE ";
-            sql += "        psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
-            sql += "        psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
-            if ( deSum != null )
-            {
-                sql += " dataelementid=" + deSum + " AND ";
-            }
-            if ( useCompletedEvents != null )
-            {
-                sql += " psi_1.completed = " + useCompletedEvents + " AND ";
-            }
-            sql += "        psi_1.organisationunitid in (" + TextUtils.getCommaDelimitedString( orgunitIds ) + ") AND ";
-            sql += "        psi_1.programstageid=" + programStage.getId() + " ";
-            sql += filterSQL + " LIMIT 1 ) as \"" + aggregateType + "\" ) ";
-
-            sql += " UNION ";
-        }
-
-        sql = sql.substring( 0, sql.length() - 6 );
-        if ( limit != null )
-        {
-            sql += " LIMIT " + limit;
-        }
-
-        return sql;
-    }
-
-    /**
      * Aggregate report Position Data Rows
      * 
      **/
@@ -1861,33 +1873,39 @@
             Collection<Integer> allOrgunitIds = getOrganisationUnits( root, facilityLB );
             Collection<Integer> orgunitIds = getServiceOrgunit( allOrgunitIds, period );
 
-            sql += "(SELECT pdv_1.value, " + aggregateType + "(pdv_1.value) as \"" + aggregateType + "\" ";
-            sql += "FROM patientdatavalue pdv_1 ";
-            sql += "    JOIN programstageinstance psi_1 ";
-            sql += "            ON psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
-            sql += "WHERE ";
-            sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
-            if ( useCompletedEvents != null )
-            {
-                sql += " psi_1.completed = " + useCompletedEvents + " AND ";
-            }
-            sql += "    psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
-            sql += "    psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
-            sql += "    psi_1.organisationunitid in( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " )  ";
-            if ( deGroupBy != null )
-            {
-                sql += " AND pdv_1.dataelementid=" + deGroupBy + " ";
-            }
-            sql += filterSQL + " ";
-            sql += "GROUP BY pdv_1.value )";
-            sql += " UNION ";
+            if ( orgunitIds.size() > 0 )
+            {
+                sql += "(SELECT pdv_1.value, " + aggregateType + "(pdv_1.value) as \"" + aggregateType + "\" ";
+                sql += "FROM patientdatavalue pdv_1 ";
+                sql += "    JOIN programstageinstance psi_1 ";
+                sql += "            ON psi_1.programstageinstanceid = pdv_1.programstageinstanceid ";
+                sql += "WHERE ";
+                sql += " psi_1.programstageid=" + programStage.getId() + " AND ";
+                if ( useCompletedEvents != null )
+                {
+                    sql += " psi_1.completed = " + useCompletedEvents + " AND ";
+                }
+                sql += "    psi_1.executiondate >= '" + format.formatDate( period.getStartDate() ) + "' AND ";
+                sql += "    psi_1.executiondate <= '" + format.formatDate( period.getEndDate() ) + "' AND ";
+                sql += "    psi_1.organisationunitid in( " + TextUtils.getCommaDelimitedString( orgunitIds ) + " )  ";
+                if ( deGroupBy != null )
+                {
+                    sql += " AND pdv_1.dataelementid=" + deGroupBy + " ";
+                }
+                sql += filterSQL + " ";
+                sql += "GROUP BY pdv_1.value )";
+                sql += " UNION ";
+            }
         }
 
-        sql = sql.substring( 0, sql.length() - 6 ) + " ";
-        sql += "ORDER BY  \"" + aggregateType + "\" desc ";
-        if ( limit != null )
+        if ( !sql.isEmpty() )
         {
-            sql += " LIMIT " + limit;
+            sql = sql.substring( 0, sql.length() - 6 ) + " ";
+            sql += "ORDER BY  \"" + aggregateType + "\" desc ";
+            if ( limit != null )
+            {
+                sql += " LIMIT " + limit;
+            }
         }
 
         return sql;