
1. Re: SQL returns error in calculation
Powerbook Oct 12, 2015 1:38 AM (in response to 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 Oct 12, 2015 3:27 AM (in response to Powerbook)The result of an ExecuteSQL is always text and numberresults form ExecuteSQL always have the "." as decimalseparator. 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 Oct 12, 2015 3:34 AM (in response to Menno)Additional to the "decimalpoint issue": you can create a litte Custom Function:
Decimal = Middle ( pi ; 2 ; 1 )
The numberresults from ExecuteSQL always have "." as decimalpoint, so if your evaluate any ExecuteSQL with a numberresult, 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 Oct 12, 2015 3:49 AM (in response to 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 Oct 12, 2015 3:57 AM (in response to Powerbook)As long as any number in the sum() does not contain a fraction and therefore doesn't contain a decimalpoint, 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 decimalpoint. 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 Oct 12, 2015 4:01 AM (in response to 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 Oct 12, 2015 4:20 AM (in response to Powerbook)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 & nonusenglish versions of FM.
beverly

8. Re: SQL returns error in calculation
Powerbook Oct 12, 2015 5:07 AM (in response to beverly)Coo
Thx Beverly