Problem with a numerical SQL function

[n7mafia]

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).

[Lee Smith]

Automatic message

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

[Rick Whitelaw]

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 +?

[n7mafia]

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.

[mr_vodka]

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

[n7mafia]

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 )
           )

[IdealData]

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!!

[n7mafia]

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

[Wim Decorte]

LEVEL is also a reserved keyword in SQL

[n7mafia]

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

[Wim Decorte]

Works here.  See attached demo

ExecuteSQL_test.fmp12.zip

[n7mafia]

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.

[Wim Decorte]

should work just the same.

[n7mafia]

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

[Wim Decorte]

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.