SQL Call to Filemaker

Post Reply
rgpepper
Member
Posts: 80
Joined: Wed Oct 14, 2015 2:09 am

SQL Call to Filemaker

Post 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.
r.zegwaard
Member
Posts: 93
Joined: Fri Jul 08, 2011 10:31 am
Location: The Netherlands

Re: SQL Call to Filemaker

Post by r.zegwaard »

You could try escaping the double quotes like:
SELECT Plan FROM Job WHERE Job_ID LIKE (NUMVAL ('[Email.Subject:After=\"#\",Before=\":\"]'))
rgpepper
Member
Posts: 80
Joined: Wed Oct 14, 2015 2:09 am

Re: SQL Call to Filemaker

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