I successfully implemented a SQL function in my database thanks to Mr. Vodka, I tried to 'recycle' it to evaluate a numeric field but it doesn't work, what am I missing?

GetValue (

ExecuteSQL ( "
              SELECT
              Left(Position + '             ',12),
                  Count ( Position ) AS kount
              FROM HighScores
              GROUP BY Position
              ORDER BY kount DESC
             ";
              "     "; ""; ""
           )

            ; Get ( CalculationRepetitionNumber )
           )

Position is a numeric field, if I replace that field with another text field the function works just fine (it counts the instances and present me a top three).

Automatic message

This topic has been moved from "Calculation Engine (Define Fields)" to "FQL - Internal SQL".

Left(Position +' ' etc. looks strange to me. I don't believe the syntax is correct. I'm on an iPad and can't test. Perhaps you need & instead of +?

The Left(Position etc... works, it tells the function to put some spaces after the result and to take the first 12 characters; infact if I change the field to a non-numeric one it works just fine; anyways I tried to replace the line you mentioned with "Position," and it doesn't work anyways.

SQL has stricter rules for data than FM. Why are you putting spaces in there again?

The Left thing and the spaces are to tell SQL to display neatly the two 'columns'; anyways, even if I put simply the field name it does NOT work since 'Position' is a numeric field, do I need a different function to handle numbers and count like in the other instance you helped me solve?

GetValue (

ExecuteSQL ( "
              SELECT
              Position,
                  Count ( Position ) AS kount
              FROM HighScores
              GROUP BY Position
              ORDER BY kount DESC
             ";
              "     "; ""; ""
           )

            ; Get ( CalculationRepetitionNumber )
           )

I'm very much SQL novice, but I downloaded the sample and tinkered...

 

POSITION is a reserved SQL keyword - rename your field. See the ODBC/JDBC docs

 

I really have to thank you for forcing me to learn this one!!

'Position' is a sample name, the real field's called 'Level'.

LEVEL is also a reserved keyword in SQL

I renamed the 'Level' field into 'Lvlqwerty' and it still returns a "?".

Works here.  See attached demo

ExecuteSQL_test.fmp12.zip

I am a bit confused. Maybe I should put it in a script as you did to make it work? Because the result's given in a window and I'd want to get it in a field.

should work just the same.

If I use the same formula in a calculation field instead of returning the wanted result it gives me a "?", I believe I am missing something? The thing I fail to understand is that if I use a text field in the SQL formula it works just fine.

P.S. I Included an example of what I would like to achieve.

top3_countSample.zip

You have a couple of things that are off:

 

1) there is a typo in the SQL statement: Count ( Positr )  --> remove the "r"

2) the field is set up to return a numeric value but the calc itself will return a text string --> change the field result to text

 

To guard against 1), where you use hard-coded field names and table names, use get(fieldname) and parse out the part that you need.  That embeds a real reference to the field.