7 Replies Latest reply on Nov 29, 2013 12:36 AM by skwan

    Table Lookup Sum feature

    Bannikov

      Hello!

       

      I had just used AF Table Lookup (2.5, of course) SUM feature. It works wonderful, when table contain some data relevant to query. But if table doesn't contain data, SUM result is NULL (not 0, as I've expected). But when I tried COUNT, COUNT gave me 0 as a result (what's appropriate because no data is zero data). I wonder if NULL is designed value for SUM with no data (or maybe 0 is more applicatible for this case).

        • Re: Table Lookup Sum feature
          Rick Davin

          This is the behavior I would expect from table lookups.  If aggregating against AFValues with nothing found, I would expect the sum to come back as NaN and the count=0.  Depending upon circumstances, NULL or NaN or even a system state of "No Result" would be appropriate, but I tend to think SUM=0 should never be used to represent a no data situation.

           

          One alternative would be to pad your lookup with another attribute that checks for such 'corner cases'.  The other attribute would be a simple formula to check for a badval returned from the lookup and then return some appropriate value (again suggest NaN or "No Result").

            • Re: Table Lookup Sum feature
              Bannikov

              Rick Davin

              This is the behavior I would expect from table lookups

               

              Any comments from OSIsoft ?

                • Re: Table Lookup Sum feature

                  I like Rick's answer, wish there was a +1/Like/UpVote button...(hint, hint).

                   

                  Without putting words in OSIsoft's mouth, like I would ever do that, I don't think they have designed anything. If you perform a sum in SQL Server or Oracle (probably the other rdbms players too) on null values you get the default of null as the result, unless you coalesce the null for a 0. Seems to me the Data Reference follows already established design patterns.

                    • Re: Table Lookup Sum feature
                      Bannikov

                      Rhys @ Wipro

                      If you perform a sum in SQL Server or Oracle (probably the other rdbms players too) on null values you get the default of null as the result

                       

                      But it would be very nice if I had such possibility to coalesce null to 0 in one plug-in (Table Lookup, I mean). I understand that I can use additional Formula-based attribute to do this, but it's too heavy, I think

                        • Re: Table Lookup Sum feature
                          skwan

                          Dear Sergey:

                           

                          This is as designed.  As you know, Zero is not the same as NULL.  Therefore, if there's no good data to sum, you will get back a NULL.

                            • Re: Table Lookup Sum feature
                              Bannikov

                              Dear Steve,

                               

                              one more comment about it. I can agree with you that in this case NULL is more appropriate that 0 and in come particular cases I can handle it differently with additional Formula.

                               

                              But we encountered a small problem connected with UOM conversion. Please see included screenshots.

                               

                              On first screenshot we receive NULL as result of Table Lookup. Please note that UOM in Table Lookup properties is specified as Default. That's okay.

                               

                              On second screenshot we also should receive NULL (data and other conditions are the same - except one thing). But we receive exception message because we'd specfied UOM in Table Lookup properties as non-default UOM (actually it's the same UOM as default UOM). I suppose that in this case we also should receive NULL value as in previous case. Can be this considered as an issue?

                               

                              Case 1

                               

                              0407.01.png

                               

                              8030.01A.png

                               

                              Case 2

                               

                               

                               

                              4331.02A.png