14 Replies Latest reply on Apr 17, 2015 9:06 AM by justinc

    Fixing empty values in the result of a eSQL: an idea

    siplus

      Hi all,


      we do a lot of graphic representation of measurements data extracted via ExecuteSQL from Filemaker tables.

      We use the very powerful xmChart plugin for plotting the data.

       

      The problem is that the lists we calculate have often segments of missing data, like 20 in a row.

       

      Otoh, xmChart expects a value for each list element, and a ¶ will simply cause the list to shrink, causing a misalign of the y data.

       

      What it wants (and will accept) is NULL as a placeholder for no data.

       

      Example:

       

      20                                          20                                                  20

      30                                          30                                                  30

                                                    15                                                  NULL

                             --->                 17           but wanted is                  NULL

      15                                                                                                15

                                                                                                          NULL

      17                                                                                                17

       

       

      Of course the received list can be post processed, with loops or recursive custom functions, but what I wanted was to get the data already ok.

       

      I also did not want to create a calc field in the original data and pull that via SQL as the data files can have milions of records and adding a calc would have been a byte waste.

       

      I think that others might have similar problems (maybe want a 0 instead of my NULL) so here is the way I solved it:

       

      Instead of SELECT myValue FROM ...

       

      I use

       

      SELECT

       

       

      STRVAL(myValue) ||

         CASE

             WHEN

             STRVAL(myValue) IS NULL

             THEN 'NULL'

              ELSE ''

         END

       

       

      FROM ...

       

       

       

       

      hope this will help somebody.

       

      Attached: using 0 as default for empty (instead of NULL), in filemaker graph.