abundance search [more fixes]

jknelsonPSU · web-flow · commit 8ab1e24d3d1e · 2021-03-19T19:28:46.000-04:00
This commit addresses an issue in the explorer search database function, which was returning datasets without respecting user-defined abundance threshold parameters. This commit also accounts for situations in which the sum of values attempts to be divided by zero in generating the average abundance value for a site, which would result in a server error, failed api call, and unresponsive explorer application.
diff --git a/function/ap/explorersearch.sql b/function/ap/explorersearch.sql
@@ -82,7 +82,7 @@ BEGIN
                 FROM    ndb.ecolgroups eg
                         JOIN ap.pollensumgroups sg ON eg.ecolgroupid = sg.ecolgroupid
                 WHERE
-                    eg.taxonid IN (_taxonids[0])
+                    eg.taxonid IN (_taxonids[1])
                 );
             IF sumGroupId > 0 THEN
                 doAbund := true;
@@ -108,7 +108,12 @@ BEGIN
               BEGIN
                 cteBaseSelect := cteBaseSelect || ',
                     v.taxonid,
-                    CAST(d.value / SUM(d.value) OVER(PARTITION BY s.sampleid) * 100 AS DECIMAL(5,2)) AS abundance';
+                    (CASE WHEN (SUM(d.value) OVER(PARTITION BY s.sampleid)) IS NOT NULL AND 
+					          (SUM(d.value) OVER(PARTITION BY s.sampleid)) <>0 THEN
+						            (CAST(d.value / SUM(d.value) OVER (PARTITION BY s.sampleid) * 100 AS DECIMAL(5,2))) 
+					          ELSE
+						            NULL END
+   					        ) AS abundance';
                 cteBaseFrom := cteBaseFrom || '
                     JOIN ndb.taxa t ON v.taxonid = t.taxonid
                     JOIN ndb.ecolgroups e ON t.taxonid = e.taxonid
@@ -177,7 +182,7 @@ BEGIN
         IF doAbund THEN
           cteAgesWhere := cteAgesWhere || '
               AND base.abundance > ' || _abundpct ||
-              ' AND base.taxonid IN (' || array_to_string( _taxonids ,',') || '))';
+              ' AND base.taxonid IN (' || array_to_string( _taxonids ,',') || ')';
         END IF;
 
         IF NOT (_ageold IS NULL AND _ageyoung IS NULL  AND noTaxa = false) THEN
