SQL Call to Filemaker
Posted: Fri Feb 09, 2018 11:49 pm
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.
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.