1 2 Previous Next 27 Replies Latest reply on Mar 6, 2015 6:12 PM by perren

    SQL: way to return 0 instead of null?

    openspace

      I have the following function that selects the total donations and then reorders them by month. I'm using this data to feed a bar chart that will list the months 1–12, however if there are no donations made in a given month, the function returns nothing which means there are less than 12 months showing on my bar chart. Not ideal.

       

      ExecuteSQL(
      "SELECT SUM(Total), MonthReorder
      FROM Donations 
      WHERE DateCreation >= StartDate AND DateCreation <= EndDate 
      GROUP BY MonthReordeR
      ORDER BY 2 ASC
      "; ¶; ¶)
      

       

      returns

       

      2724.22

      a

      574.61

      b

      229.61

      c

      695

      d

      2000

      e

      1101

      f

      500

      h

      870

      i

      150

      j

      17

      k

      20

      l

       

      where every letter represents a month. As you can see there were no donations made during month "g" so it returns nothing, but I would actually like the result to show a value of 0 instead of null as shown below:

       

      2724.22

      a

      574.61

      b

      229.61

      c

      695

      d

      2000

      e

      1101

      f

      0

      g

      500

      h

      870

      i

      150

      j

      17

      k

      20

      l

        • 1. Re: SQL: way to return 0 instead of null?
          nicolai

          We unfortunately do not have table variables in FQL (and I don't think we will see them in the next version). This would be the first thing to try. So, my preferred solution would be to create a months table and populate it with 12 records, then LEFT OUTER JOIN on this table.

           

          The second option is to mix FileMaker calc with FQL. Since you know your target set months, you can add 12 calculations (I only put two to keep it short). This should give you desired output with all 12 months listed and 0 for Total in missing months:

           

          ExecuteSQL(

          "SELECT SUM(Total)

               FROM Donations 

               WHERE DateCreation >= StartDate

               AND DateCreation <= EndDate 

               AND MonthReorder  ='a'

          "; ""; "")  + 0 & "¶a¶"  &

           

          ExecuteSQL(

          "SELECT SUM(Total)

               FROM Donations 

               WHERE DateCreation >= StartDate

               AND DateCreation <= EndDate 

               AND MonthReorder  ='b'

          "; ""; "")  + 0 & "¶b¶"

          • 2. Re: SQL: way to return 0 instead of null?
            siplus

            writing it as pseudocode, hope you get it:

             

            reorderstring = "a¶b¶c¶d¶e¶f¶g¶h¶i¶j¶k¶lm¶n¶o"

            $i = 0

            Loop

            exit loop if    let ($i = $i + 1; $i > 12)

            setvar m = getValue ( reorderstring ; $i)

            setvar $sql = ExecuteSQL("SELECT SUM(Total) FROM Donations WHERE DateCreation >= StartDate AND DateCreation <= EndDate and MonthReorder = ?";"";""; m)

             

            $result[$i] = Choose(GetAsBoolean($sql); 0; $sql)

             

            End loop

             

            return list($result)

            • 3. Re: SQL: way to return 0 instead of null?
              user19752

              Another way, not tested

               

              ExecuteSQL("SELECT

              SUM(CASE WHEN Month(DateCreation)=1 THEN Total ELSE 0 END),

              SUM(CASE WHEN Month(DateCreation)=2 THEN Total ELSE 0 END),

              ...

              SUM(CASE WHEN Month(DateCreation)=12 THEN Total ELSE 0 END)

              FROM Donations WHERE DateCreation >= StartDate AND DateCreation <= EndDate

              ";¶;"")

               

              Making month field or repeated 12 calculation CASE..END field may be good for performance.

              In this way, you can get any number of years using GROUP BY year(DateCreation) with one SQL.

              • 4. Re: SQL: way to return 0 instead of null?
                beverly

                CASE() sql-function is absolutely a good way to do this!

                beverly

                • 5. Re: SQL: way to return 0 instead of null?
                  openspace

                  user19752

                  This is what I'm looking for. I've called upon multiple sqls for each month in the past and performance is daunting. I've adapted my code to the following but I'm getting a "?" as a return value.

                   

                  ExecuteSQL( "SELECT

                  SUM(CASE WHEN MonthReorder=a THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=b THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=b THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=c THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=d THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=e THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=f THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=g THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=h THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=i THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=j THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=k THEN Total ELSE 0 END),

                  SUM(CASE WHEN MonthReorder=l THEN Total ELSE 0 END)

                  FROM Donations  

                  WHERE DateCreation >= StartDate AND DateCreation <= EndDate  

                  "; ¶;"")

                  • 6. Re: SQL: way to return 0 instead of null?
                    perren

                    You could try wrapping your SUM function in a COALESCE like so:

                     

                    ...

                    SELECT COALESCE ( SUM(Total), 0 ), MonthReorder

                    ...

                     

                    COALESCE returns the first non NULL result, so if there's a total great, if not, it'll return 0.

                     

                    --Perren

                    • 7. Re: SQL: way to return 0 instead of null?
                      Markus Schneider

                      COol! Have to check this out.. A kingdom for a way to categorize postings - so that we can find (i.e.) SQL stuff easily later (-:

                      • 8. Re: SQL: way to return 0 instead of null?
                        user26142

                        You might adapt:

                         

                        SELECT ISNULL (column_name,0) AS col_alias  FROM table_name

                         

                         

                        I haven't tested this in FM - works in Oracle.

                        • 9. Re: SQL: way to return 0 instead of null?
                          nicolai

                          There is "IS NULL" as in "WHERE mycolumn IS NULL" but there is no dedicated ISNULL function (same function exists in TSQL).

                          There is COALESCE as Perren Smith mentioned in the previous post, it does the same thing, but better.

                           

                          The post was edited after Beverly's comment I managed to omit no in the original which reverted the meaning. Friday night!

                           

                          but there is dedicated ISNULL function  -> changed to -> but there is no dedicated ISNULL function

                          • 10. Re: SQL: way to return 0 instead of null?
                            nicolai

                            Just to confirm - works fine for me.

                             

                            Change MonthReorder=a  to MonthReorder='a' for all lines accordingly, I think this is what question marks your query

                             

                            Make sure that Total is evaluated as number.


                            As Beverly Voth recommended before in your other post - use parameters in your queries for StartDate and EndDate


                            user19752  - thanks, good solution

                            • 11. Re: SQL: way to return 0 instead of null?
                              beverly

                              I tried to test *every* SQL function when writing the ExecuteSQL() reference. Those for Oracle, those for MS SQL and those for MySQL (yes, they had some different functions named with the same results)

                               

                              I don't believe ISNULL() works with ExecuteSQL(). However COALESCE() does work well in FileMaker. It has an added benefit with the output of dates and times and numbers. These are more as FileMaker would give them with Date(), Time(), etc.

                               

                              beverly

                               

                              p.s. reference to the these funtions: SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions

                              • 12. Re: SQL: way to return 0 instead of null?
                                nicolai

                                !!! damn. That's that I tried to say and I manage to skip on "no".  --> edited my post now, so it probably makes more sense.

                                In TSQL COALESCE has an advantage to test more than two parameters. FM SQL reference does not mention this, I will try when I can if it is possible.

                                • 13. Re: SQL: way to return 0 instead of null?
                                  beverly

                                  here's another article on COALESCE()

                                  COALESCE vs. ISNULL T-SQL Functions | T-SQL content from SQL Server Pro

                                   

                                  What actually works in FMP with ExecuteSQL(), of course is subject to testing!

                                  If you come up with something NOT mentioned in 'the refernce', freely post to the blog!

                                  The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

                                   

                                  While this says 12, the additional functionality for eSQL in 13 are well covered here

                                  http://www.seedcode.com/filemaker-13-sql/

                                  • 14. Re: SQL: way to return 0 instead of null?
                                    nicolai

                                    Many thanks, Beverly Voth and will do.


                                    In case you can't wait - I tried it and the number of parameters for COALESCE() is not restricted. Happy days, that's actually a better implementation than ISNULL.

                                    1 2 Previous Next