sql queries in FMP 10 advanced

[ejpvi]

Greetings,

I have a sql query I want to run... but I think some of the clauses in it are not available in Filemaker's Query builder, which only has SELECT WHERE ORDERBY. Can I type in the query manually to get this to work, or will only those three clauses work? Or is there another way to request a SQL query that I don't know about?

[beverly]

You can create a calculation and call that. I just don't use the "builder", it is limiting. :

[ejpvi]

I think I tried that, and Filemaker acted like it didn't know the functions.

select count(orderno) as number_orders, custnum into #temp from cms group by custnum

select * from #temp where number_orders=1

select * from cust where custnum in (select custnum from #temp where number_orders=1)

This Query would work if I went directly to the SQL server and ran it... but I am trying to avoid interacting with the server, other than using FMP....

I think the problem is, this particular query would return a lot of results.. I wonder how I could do this in FMP... since the calculated field is built for one result...

Any Ideas?