SQL Call to Filemaker

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

SQL Call to Filemaker

Post by rgpepper » 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.

r.zegwaard
Member
Posts: 41
Joined: Fri Jul 08, 2011 10:31 am

Re: SQL Call to Filemaker

Post by r.zegwaard » Mon Feb 12, 2018 8:32 am

You could try escaping the double quotes like:
SELECT Plan FROM Job WHERE Job_ID LIKE (NUMVAL ('[Email.Subject:After=\"#\",Before=\":\"]'))

rgpepper
Member
Posts: 55
Joined: Wed Oct 14, 2015 2:09 am

Re: SQL Call to Filemaker

Post by rgpepper » Mon Feb 12, 2018 4:24 pm

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