12 Replies Latest reply on Jun 3, 2017 4:33 AM by fmpdude

    Using Execute SQL to do calculation of values based on different columns criteria

    camcorp

      Hi Community

      I'm doing the following calculation from a table of a journal book.

      the table structure is as follows

      rdGPZ.png

      and the result of the query should be

      rMhFS.png

      For Month 1 the total amount  based on column LEVEL1 "Gross Margin' should be  400  [1500-1100) divided by the total amount based on column LEVEL0 "Income" [1500] , the result for Month1 should be 400/1500=0,27

      My SQL Statement is as follow

       

      SELECT Month,
        SUM
      (CASE WHEN Level1 = 'Gross Margin' THEN Amount END)/
        SUM
      (CASE WHEN Level0 = 'Income' THEN Amount END) as Ratio
      FROM My Table
      GROUP BY Month;

       

      Something is not working, please help and thanks in advance

       

        • 1. Re: Using Execute SQL to do calculation of values based on different columns criteria
          wimdecorte

          Define 'not working'.  Are you getting different numbers than you expect?  Or are you getting "?" as the result? If the latter then it is a syntax issue.  If the former it is a logic issue.

          • 2. Re: Using Execute SQL to do calculation of values based on different columns criteria
            camcorp

            Hi,

            I'm getting a "?" result.

            I'm splitting the query to find where is the error.

            thanks!

            • 3. Re: Using Execute SQL to do calculation of values based on different columns criteria
              beverly

              What error are you getting? The "?" is not helpful, so you might use the

              EvaluationError()

              around the ExecuteSQL() to return an error code.

              Also not so helpful, unless the code matches something in the FMP error codes. If you have FMPAdvanced there are ways to test in the Data Viewer and get a message for the error.

               

              You may actually be getting a division-by-zero error or anything that would normally throw an error in a calculation.

               

              Have you tried putting the two values into two fields (or two variables) as separate queries to test what values you get?

              (one for Level0, one for Level1). Those values may help you see what problem you have.

               

              Beverly

              • 4. Re: Using Execute SQL to do calculation of values based on different columns criteria
                erolst

                In additon to what Wim and Bev said:

                camcorp wrote:

                 

                SELECT Month,
                SUM
                (CASE WHEN Level1 = 'Gross Margin' THEN Amount END)/
                SUM
                (CASE WHEN Level0 = 'Income' THEN Amount END) as Ratio
                FROM My Table
                GROUP BY Month;

                 

                Something is not working, please help and thanks in advance

                 

                you need to quote object names that contain spaces (or are keywords etc.) - \"My Table\" - so if the above is your actual code, there's already one thing to correct ...

                2 of 2 people found this helpful
                • 5. Re: Using Execute SQL to do calculation of values based on different columns criteria
                  camcorp

                  Hi

                  I just split the two CASE statement and they worked fine separately. but when doing the division between the two results I get the Sign "?" . Using evaluationerror i get the code 8309

                  • 6. Re: Using Execute SQL to do calculation of values based on different columns criteria
                    fmpdude

                    Post your data and I'll try the query for you and get it working.

                     

                    -----

                    Check out an external SQL tool like RazorSQL so you get all the powerful SQL tools like SQL assist, real error messages, etc. And, yes, it connects directly to your live FMP database. Using an external tool, I've saved many hours of frustration after getting the maddening "?"

                     

                    FMP 16 supposedly also gives real SQL error messages, but other than that the Data Viewer is more or less the same once it pastes in the template...you're own your own from there.

                     

                    HOPE THIS HELPS.

                    • 7. Re: Using Execute SQL to do calculation of values based on different columns criteria
                      beverly

                      Ugh! the dreaded "8309" = Semantics error (logical)

                      Also not so helpful.

                       

                      ExecuteSQL() is unique in that it seems to follow much of what is "legal" in SQL. But then it burps all over you.

                       

                      Many of us have found that Aggregates (the SUM, for example) just seem to hate being combined with anything else. The CASE seems to be ok, but forget the other problems!

                       

                      What I have done in these times, is to create the result as needed and then "Evaluate()" the results (with caution, of course). Or post process the results (as you have with two valid values) in a scripted/calculated way.

                       

                      Add that to the GROUP BY that you need it may be more complex than desired.

                       

                      Try a "|" as the column delimiter and make it 3 columns, so you'd get:

                      month|sumLevel1|sumLevel0

                      Then because it is delimited this way you may be able to loop the values (return delimits the rows) in a script. So you get your final values as needed for a report of calculated ratios.

                       

                      Beverly

                      • 8. Re: Using Execute SQL to do calculation of values based on different columns criteria
                        erolst

                        This was to be a response to a post from fmdude that he deleted while I was answering ... well, here it is anyway:

                         

                        AFAIK, you can circumvent - at least to a point - that "non-support for expressions using aggregates" by writing the expression components as sub-queries:

                         

                        SELECT. aMonth,

                        ( SELECT SUM ( amount ) FROM MyTable t1 JOIN t ON t1.aMonth = t.aMonth WHERE ... )

                        /

                        ( SELECT SUM ( amount ) FROM MyTable t2 JOIN t ON t2.aMonth = t.aMonth WHERE ... )

                         

                        FROM MyTable t

                        ...

                         

                        or something along those lines; I tend to lose track of "where" I am in these contraptions ... but I think a real expert could make that work.

                        1 of 1 people found this helpful
                        • 9. Re: Using Execute SQL to do calculation of values based on different columns criteria
                          fmpdude

                          Yeah, I'm really not sure this can be done in a single query. You might use SQL to set up the results, but then do a calculation to get your final result.

                           

                          (Note: I did the query below in MySQL which uses the same syntax)

                          • 10. Re: Using Execute SQL to do calculation of values based on different columns criteria
                            camcorp

                            Hi ,

                            I just made it

                             

                            the query is as follows

                            Let (

                            $result =

                             

                            ExecuteSQL ( "

                            SELECT Month

                                '<',

                            SUM (CASE WHEN LEVEL1 = 'Gross Margin' THEN Amount END)

                            ,'===',

                            sum (CASE WHEN LEVEL0 = 'Income' THEN Amount END)

                            ,'>'

                            FROM MyTable

                            GROUP By Month

                            " ; "" ; "";"")

                            ;

                            Evaluate ( Substitute ( Quote ( $result ) ; [ "<," ; "\"&Round(" ] ; [ ",===," ; "/" ] ; [ ",>" ; "*100;2)&\"" ] )  )

                            )

                             

                            just an image of the chart

                             

                            Sin título.jpg

                            hope it help to you

                             

                            regards

                            Claudio

                            1 of 1 people found this helpful
                            • 11. Re: Using Execute SQL to do calculation of values based on different columns criteria
                              fmpdude

                              Yep that's what I was suggesting, at least not to rely on the query exclusively. I didn't have time to play around with this beyond what I wrote you....glad you got it working. 

                              • 12. Re: Using Execute SQL to do calculation of values based on different columns criteria
                                fmpdude

                                Here's how I did it. (note: I try to avoid nearly impossible to debug custom functions. Also the CF editor is very rudimentary (frustrating, slow to work with) with zero debugging support and you can't change fonts, etc.). The overly modal interface in FMP regularly drives me nuts. Close this window and that window before you can open this other window. Madness!

                                 

                                Scripts are also much easier to read and all versions of FMP support them.

                                 

                                Script is below.

                                 

                                The idea is that this code needs to work for any number of months, not just 2, per your example.

                                 

                                • I used paragraph returns to create a list from each SELECT statement at the top (top two lines).
                                • Then, I initialized some variables and, in the loop, I walk through the list until one of GETVALUE is empty. Then I exit the list.
                                • Also in the loop, I'm creating an output list using, again, paragraph markers.
                                • Finally, I set the "result" field with that list output.

                                 

                                 

                                NOTE: CLICK THE IMAGE IF IT DOESN'T DISPLAY COMPLETELY

                                output:

                                 

                                I'm sure some of the FMP experts here can make this better, but this is a quick rough draft, anyway, that works!

                                 

                                HOPE THIS HELPS.

                                1 of 1 people found this helpful