← Back to team overview

dhis2-devs team mailing list archive

[Branch ~dhis2-devs-core/dhis2/trunk] Rev 20002: Sql functions, evaluate to zero when null

 

------------------------------------------------------------
revno: 20002
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Sun 2015-09-06 09:15:45 +0200
message:
  Sql functions, evaluate to zero when null
modified:
  dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java
  dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java
  dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java


--
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 'dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java'
--- dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java	2015-09-04 09:29:23 +0000
+++ dhis-2/dhis-services/dhis-service-core/src/test/java/org/hisp/dhis/program/ProgramIndicatorServiceTest.java	2015-09-06 07:15:45 +0000
@@ -498,7 +498,7 @@
     public void testGetAnalyticsSqlWithFunctionsA()
     {
         String col = COL_QUOTE + deA.getUid() + COL_QUOTE;
-        String expected = "case when " + col + " < 0 then 0 else " + col + " end";
+        String expected = "coalesce(case when " + col + " < 0 then 0 else " + col + " end, 0)";
         String expression = "d2:zing(" + col + ")";
 
         assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) );
@@ -508,7 +508,7 @@
     public void testGetAnalyticsSqlWithFunctionsB()
     {
         String col = COL_QUOTE + deA.getUid() + COL_QUOTE;
-        String expected = "case when " + col + " >= 0 then 1 else 0 end";
+        String expected = "coalesce(case when " + col + " >= 0 then 1 else 0 end, 0)";
         String expression = "d2:oizp(" + col + ")";
 
         assertEquals( expected, programIndicatorService.getAnalyticsSQl( expression ) );

=== modified file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java'
--- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java	2015-08-27 22:22:39 +0000
+++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/OneIfZeroOrPositiveSqlFunction.java	2015-09-06 07:15:45 +0000
@@ -30,7 +30,7 @@
 
 /**
  * Function which evaluates numerical values to one if zero or positive, zero
- * otherwise.
+ * if negative or null.
  * 
  * @author Lars Helge Overland
  */
@@ -42,6 +42,6 @@
     @Override
     public String evaluate( String arg1, String arg2, String arg3 )
     {
-        return "case when " + arg1 + " >= 0 then 1 else 0 end";
+        return "coalesce(case when " + arg1 + " >= 0 then 1 else 0 end, 0)";
     }
 }

=== modified file 'dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java'
--- dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java	2015-08-27 22:22:39 +0000
+++ dhis-2/dhis-support/dhis-support-commons/src/main/java/org/hisp/dhis/commons/sqlfunc/ZeroIfNegativeSqlFunction.java	2015-09-06 07:15:45 +0000
@@ -29,8 +29,8 @@
  */
 
 /**
- * Function which evaluates numerical values to zero if negative, unchanged if
- * zero or positive.
+ * Function which evaluates numerical values to zero if negative or null, unchanged 
+ * if zero or positive.
  * 
  * @author Lars Helge Overland
  */
@@ -42,6 +42,6 @@
     @Override
     public String evaluate( String arg1, String arg2, String arg3 )
     {
-        return "case when " + arg1 + " < 0 then 0 else " + arg1 + " end";
+        return "coalesce(case when " + arg1 + " < 0 then 0 else " + arg1 + " end, 0)";
     }
 }