Page 1 of 1

SQL Call to Filemaker

Posted: Fri Feb 09, 2018 11:49 pm
by rgpepper
Trying to cross-reference a couple fields that are in Filemaker Server (hosted on a separate box - both are Windows Server), using database connect via ODBC, trying to do a SQL call. I have successfully made other SQL calls to Filemaker. But I'm stumped. I either get a syntax error or "an expression contains incompatible data", couple of examples:

SELECT Plan FROM Job WHERE Job_ID LIKE (NUMVAL ('[Email.Subject:After="#",Before=":"]'))

SELECT Plan FROM Job WHERE Job_ID LIKE (NUMVAL '[Email.Subject:After="#",Before=":"]')

I'm comparing a Switch piece of metadata - the subject of an incoming email using Mail Receive. It's text, but the field I'm comparing against (Job_ID) is numeric, so I have to coerce to a numeric type(?). If I hard-code a number: SELECT Plan FROM Job WHERE Job_ID LIKE 12345, it works.
I've tried several other variations, I just don't know the exact nesting of parens, quotes and brackets to come up with the appropriate data and pass it off.

Re: SQL Call to Filemaker

Posted: Mon Feb 12, 2018 8:32 am
by r.zegwaard
You could try escaping the double quotes like:
SELECT Plan FROM Job WHERE Job_ID LIKE (NUMVAL ('[Email.Subject:After=\"#\",Before=\":\"]'))

Re: SQL Call to Filemaker

Posted: Mon Feb 12, 2018 4:24 pm
by rgpepper
This worked:
SELECT Plan FROM Job WHERE Job_ID = (NUMVAL('[Email.Subject:After="#",Before=":"]'))

Not sure if it was switching to "=" and because it's numeric as I thought I understood that "LIKE" should work.