HELP - Cannot get working example of SQL Query returning a literal

[mattp52]

Hi,

I'm attempting to return a literal out of an ExecuteSQL() statement so I can test and develop another SQL query but I cannot get it to return successfully (it reports SQL Syntax error). I used verbatim this model example from Beverly on the FMForums only changing the "myTableRef" reference to a valid TO name in my solution but no results are returned.

Ultimately I want to coalesce or cast a text field to an integer so I can use the value in a "WHERE ... IN()" SQL statement expecting integers. I had no success using CAST, COALESCE or NUMVAL SQL functions doing this which lead me to this use of literals which I was hoping to use to debug my casting problem.

I'm using FM Pro Advanced v14 / El Capitan. Thanks in advance for any help you can provide.

Let ( 

[ _pre = " '$' " // change as needed

; _num = Round( 2345.017; 2) // change to actual field ref & round first

; _query = " SELECT "

& If ( not IsEmpty(_pre); _pre & "||" )

& " STRVAL( COALESCE( INT( " & _num & "  ) , 0 ) ) "

& " || '.' "

& " || SUBSTR( STRVAL( " & _num & " - INT( " & _num & " ) ) || '000', 2, 2 ) "

& " FROM myTableRef "

; result = ExecuteSQL ( _query ; "" ; "" )

]; 
result
//""

) // $2345.02

 

[Wim Decorte]

Not sure I follow, is the expected result this:  $2345.02 ?

That's not an INT, that's a string.  I don't see how you expect to pass something with a "$" prefix to your WHERE ...IN () clause if that expects numbers.

 

[beverly]

What error (if any) are you getting? do you have FMPAdvanced and can test this in Data Viewer? 

beverly

[mattp52]

Hi,

Thanks for the replies. I quoted the example above as a FM SQL statement with casting that should work but is not working in my environment but its a different solution to the one I need. When I run the example above in the data viewer of FM 14 Advanced Pro I get the "?" as a return result. I've reduced the query in complexity even further - I don't get an error now, just no result. The "_query" var  itself resolves to "SELECT  INT( 2345.02  )  FROM UTILITY"

Let ([ 
_num = Round( 2345.017; 2);

_query = "SELECT " & " INT( " & _num & "  ) " & " FROM UTILITY ";
result = ExecuteSQL ( _query ; "" ; "" )];
 
result
) // Expected $2345.02, get ""

No SQL errors are displayed in the data viewer if I substitute "" for the retutn value of the Let() statement.

In my specific scenario I have a list of integer values I want to match with a WITH.. IN() statement.  However the field reference I am passing to the Let() statement is sometimes a text field and sometimes an integer (the script is called dynamically from a loop so different fields are passed during any invocation).

The SQL Statement resolves to "SELECT * FROM UTILITY E WHERE E.zzRecordID IN(1,2,3)"

Let([
	ids = List(1;2;3);
	ids = Substitute ( ids; "¶" ; "," ); // 1,2,3
	fk = "fieldName";	
	result = ExecuteSQL ( "SELECT * FROM UTILITY E WHERE E." & fk & " IN(" & ids & ")" ; ""; "" )
];
	result
)

The query returns "?". Substituting empty quotes for the return value reveals the error "An expression contains data types that cannot be compared." The data type of E.fieldName in this case is varchar,14,Normal,1,9 (as returned from a query to FileMaker_Fields table).

I assumed I could cast that field reference in the statement to eliminate the error but have had no success with any of the following...

"SELECT * FROM " & table & " E WHERE CAST(E." & fk & " AS DOUBLE) IN(" & ids & ")" // returns "?"
 > There is an error in the syntax of the query.

"SELECT * FROM " & table & " E WHERE COALESCE( INT(E." & fk & ")) IN(" & ids & ")" // returns "?"
 > "Parameter number 1 to the function "INT" is not of the correct type.

"SELECT * FROM " & table & " E WHERE NUMVAL(E." & fk & ") IN(" & ids & ")" // returns "?"
 > No SQL Error Reported

So how can I match the data types between the field ref and the values in the IN() statement, preferably to always force a comparison of integer values?

Thanks again!

 

[Wim Decorte]

30 minutes ago, mattp52 said:

So how can I match the data types between the field ref and the values in the IN() statement, preferably to always force a comparison of integer values?

 

Well, either you know in advance and can do the proper Substitute() call to add the proper quotes or format dates etc.

Or you can write a script to dynamically allow for as may "?" placeholders in the query and parameter places in the ExecuteSQL().

Not that hard to do.  The idea is that filemaker will allow something like this:

ExecuteSQL( "SELECT something FROM sometable WHERE somefield IS IN (?,?,?,?)", "", "": param1 ; param2 ; param3 ; param4)

and it will do the proper usual quoting and date/time formating for you.  So constructing the proper syntax is nothing  more than a text parsing exercise...

 

 

[mattp52]

Quote

Well, either you know in advance and can do the proper Substitute() call to add the proper quotes or format dates etc.

Not sure I understand what you mean here. For my example in the WHERE..IN statement I tried to match on values formatted as  "(1,2,3)" "('1', '2', '3') and ("1", "2", "3") and none were successful.

Quote

Or you can write a script to dynamically allow for as may "?" placeholders in the query and parameter places in the ExecuteSQL().

Ok, so I got the query to resolve data using static values in a query so I see where the data type substitution is performed successfully, thanks. However, I don't see how I can dynamically construct an "ExecuteSQL()" string as it doesn't appear that such statements can be passed through Evaluate() and resolved. Is this correct?

I also can't create some sort of cascading CASE condition containing multiple ExecuteSQL statements each with a different number of replacement parameters. The number of params required is entirely end-user dependent and not predictable.

It would be so much easier if I could just cast the return value for the field in the SQL statement to an integer before performing the WHERE..IN() condition test.

[beverly]

numbers are not quoted in SQL, so "1" or '1' is invalid.

If you use the ? parameters in ExecuteSQL() function, then FileMaker will add the appropriate quotes:

Quote

 

WHERE x=? AND (y=?) & (z=?)

; 1 ; "abc" ;  3.5

 

will be converted to:

Quote

WHERE x=1 AND (y='abc') AND (z=3.5)

At run.

beverly

[Wim Decorte]

11 hours ago, mattp52 said:

However, I don't see how I can dynamically construct an "ExecuteSQL()" string as it doesn't appear that such statements can be passed through Evaluate() and resolved. Is this correct?

No, that is incorrect...  You can run an ExecuteSQL() call through Evaluate.

Demo attached.

SQLlog_v2.fmp12.zip