Jump to content

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


This topic is 2262 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

 

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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!

 

Link to comment
Share on other sites

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

 

 

  • Like 1
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

  • Like 1
Link to comment
Share on other sites

This topic is 2262 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.