Filemaker GetAsNumber on ExecuteSQL

Question

I have a calculation for a field like this:

GetAsNumber(
    ExecuteSQL ( 
        "SELECT nb FROM something WHERE value = ?" ;
        "" ;
        "" ;
        amount
    )
)

Everything works fine as long as the returned number is single-digit. But when the SQL statement returns for example "12", then the number I get from the GetAsNumber function is suddenly 1212 instead of 12.

As I understand it, this has somehow to do with the format in which SQL results are returned, it says so in the documentation ("FileMaker Pro returns date, time, and number data in Unicode/SQL format, not in the locale of the operating system or the file.").

But I guess there must be a way to format back the result so it can be converted to a number.

Answer 1

The solution comes from by michael.hor257k's comment on my question:

ExecuteSQL returns an array (I wasn't aware of it, it's also not specified in the documentation where it says: "Data type returned: text"). So my problem had nothing to do with double digits (I guess I should have run more tests), but with the sql statement returning more than one result.

As in my case multiple results are always the same, I could alter my calculation in this way:

GetValue(
    ExecuteSQL ( 
        "SELECT nb FROM something WHERE value = ?" ;
        "" ;
        "" ;
        amount
    ) ;
    1
)

In this way I only retrieve the first result.

As michael.hor257k suggested in his comment, I could also restrict the SQL result to one row.

ExecuteSQL ( 
    "SELECT nb FROM something WHERE value = ? FETCH FIRST ROW ONLY" ;
    "" ;
    "" ;
    amount
)