8 Replies Latest reply on Oct 12, 2015 5:07 AM by Powerbook

    SQL returns error in calculation

    Powerbook

      Yesterday something strange has happened once I tried to type a number from a numeric keypad that has "." instead of ",".

      Since I'm in Europe and we use "comma" to determine decimals, once I used the point the value appears as a full number es:

       

      Typed 120.00 and got 12000. And up to this all was ok, I prevented the error to happen again and I used during input this formula

      Substitute ( Self ; "." ; "," )

      From now on any kind of comma or point will be interpreted correctly.

      All ok up to now.

       

      Then I need to extract some numbers, I I did it with this SQL script:

       

      Sum (

      ExecuteSQL (

      "SELECT SUM (\"Entrate\")

      FROM \"Movimenti\"

      WHERE (\"Causale 3\") = ?"

      ; "" ; "" ; "P9B");

       

      ExecuteSQL (

      "SELECT SUM (\"Uscite\")

      FROM \"Movimenti\"

      WHERE (\"Causale 3\") = ?"

      ; "" ; "" ; "P9B")*-1

      )

       

      and that has happened is that I get a wrong totals number from only those field I inserted with the wrong decimal.

      Although I before edited the correct number in the table. In Fact they do display correctly on their Layouts. But NOT in the SQL Global Variable $$P9B

       

      WHY??

       

      It looks as if hidden numbers are used for calculation. I HAVE NO CLUE!!

       

      Can anybody help me?

      Thank you

        • 1. Re: SQL returns error in calculation
          Powerbook

          actually is not exactly an error.

          The number to be Displayed is es:

           

          5675,52 and the global variable displays 5675.52000000

           

          Weird

           

          ps:

          System settings are to display decimals with a comma "," and they do that in layouts.

          • 2. Re: SQL returns error in calculation
            Menno

            The result of an ExecuteSQL is always text and number-results form ExecuteSQL always have the "." as decimal-separator. So adjust your formula like this:

            Sum (

            Substitute (

            ExecuteSQL (

            "SELECT SUM (\"Entrate\")

            FROM \"Movimenti\"

            WHERE (\"Causale 3\") = ?"

            ; "" ; "" ; "P9B");

             

            ExecuteSQL (

            "SELECT SUM (\"Uscite\")

            FROM \"Movimenti\"

            WHERE (\"Causale 3\") = ?"

            ; "" ; "" ; "P9B") ; "." ; "," ) *-1

            )

            and it should work just fine.

            • 3. Re: SQL returns error in calculation
              Menno

              Additional to the "decimal-point issue": you can create a litte Custom Function:

              Decimal = Middle ( pi ; 2 ; 1 )

              The number-results from ExecuteSQL always have "." as decimal-point, so if your evaluate any ExecuteSQL with a number-result, you could use a CF like:

              NumberFromSQL = Substitute ( ResultFromSQL ; "." ; Decimal )

              There are about a thousand different approaches to this, but you get the point i think

              • 4. Re: SQL returns error in calculation
                Powerbook

                Thank you Menno for the solution.

                 

                What I'm still wondering, is why all the other 30 global function I have in my solution generated in the same way, produce a correct result and also keeps the "value" format of "€".

                I just know that all this mess with the decimal started when we used a PC windows computer and inserted the value via the left number keyboard that uses as decimal the "." (point)

                Since then there were no way to clear the field and reinsert it correctly. The error was always there

                I had to delete the whole record, and re insert it.

                That's why I found all this weird, because for all the other fields I have it works fine without using "SUBSTITUTE.

                 

                Do you have any clue why is this happening?

                • 5. Re: SQL returns error in calculation
                  Menno

                  As long as any number in the sum() does not contain a fraction and therefore doesn't contain a decimal-point, the numbers are OK. Just one fraction and it messes the calc up.

                  I am not sure when the result is an integer, even when fractions are included in the sum, that the result would be without a decimal-point. I haven't tested that. I just always format my result regardless the outcome, so I don't have the problem.

                  • 6. Re: SQL returns error in calculation
                    Powerbook

                    I think you made the point,

                    I will change all my variables now

                     

                    Thank you again for the help.

                    • 7. Re: SQL returns error in calculation
                      beverly

                      one thing, I have found that "helps" numbers and dates and times in ExecuteSQL(), is to be explicit when passing values:

                       

                      WHERE mydate = ?

                           AND mynum = ?

                       

                      ; GetAsDate(mydatevalue) ; GetAsNumber(mynumvalue)

                       

                      these tend to "cleanup" some values that FileMaker doesn't care about, but SQL does. this helps especially in those places where data is imported, or there can be various keyboards & non-us-english versions of FM.

                       

                      beverly

                      • 8. Re: SQL returns error in calculation
                        Powerbook

                        Coo

                        Thx Beverly