Does FMP 9 support subqueries

[pmconaway]

I have the following situation.

table_1 = person table ( information regarding people , name , id num, ....)

table_2 = list of people that attended an event. (id num, and event related information)

Table 1 is in oracle db schema (person)(1 million + records)

Table 2 is in oracle db schema (events)

I have the following query that I can't get to work.

select ID_num, First_name, last_name

from table_1 where (person.inactive_dt >= SYSDate or person.inactive_dt is null ) and person.id_num in (select unique(ID_num) from table2)

The second select is what I am questioning. I keep getting a table or view doesn't exist error. I have verified that table names are correct.

Normally I would just link table 2 and table 1 in FMP relationships screen and have FMP handle things but when I do that and try to run the reports that I need. My process consumes about 50% of the server resources and causes response issues with the server for other applications. This is a problem since most if not all of the applications that use oracle need access to table_1 (person).

What I'm trying to do is just copy the person records out of the person table in FMP for just the people that attended events. Currently the event registration table has 33000 records. I'm trying to find a work around so that everyone here at work is happy. Any suggestions are greatly appreaciated.

[Dali]

I'm not familiar with Oracle but in MS SQL you can filter the rows using a view. For example

CREATE VIEW Children

AS SELECT * FROM People WHERE Age < 10

When filemaker looks at the Children "table" only people younger than 10 will be transmitted between the Oracle and FM. Performance is also better using views since views are already compiled and can also be indexed.

Cheers,

Dali

[pmconaway]

Dali, Let me know if I'm understanding you correctly. A view is a predefined subset of a table. For instance in your example the child view could be defined as all records in the person table where age < 18. Correct?

In my example my view would be all records in the person table that have a an ID in the event registration table.

Next question, I haven't tried views in FMP can FMP access views? I have had problems in the past. (Could have been previous version of FMP. I know I haven't tried it in version 9.)

[Dali]

Your understanding is correct. I'm currently working on a project that uses FM as a front end for MS SQL. In the entire project the only thing FM has access to are views. In FM world a view appears just like another table, in fact you can't even tell the difference. But you need to make sure to deny permissions to actual tables and grant at least select permissions to views.

Dali

[mr_vodka]

The new ESS feature in FileMaker 9 was not engineered to really be a true "front end" into other sources.

However, I will agree with Dali in that taking advantage of views and stored procedures that produce views in the external source is much better than trying to filter them through FileMaker.

[pmconaway]

Thanks for the info guys. Unfortunately I not sure I can get specific views that I need created. They have the databases pretty much locked down and I'm not part of the database team. So getting the view that I need is going to be a long shot.

[mr_vodka]

I know how it is to get other groups to do stuff for you. However, if this is something that you are going to repeatedly and you show them that it is a necessity, then they should act accordingly.

Creating a view doesnt take that long and with over a million records it would improve efficiency.

[pmconaway]

I know but FMP is not officially supported. And I expect when I go and ask about a view then I will get why don't you use Access or something else. I will check though. We are still development the web application that uses this database schema. I might be able to work this view into that.

It seems like my job is sometimes just trying to over come road blocks.

[mr_vodka]

Welcome to the world of trying to proove FM to other IT folks... lol.

Also regardless of whether its Access or FileMaker, creating a view would be more efficient as they should know.

[pmconaway]

Welcome to the world of trying to proove FM to other IT folks... lol.

Also regardless of whether its Access or FileMaker, creating a view would be more efficient as they should know.

John, I don't think it is a matter of proving the efficiency of the view, it is matter of insuring that the view won't cause more havoc with the system.

Paul

[pmconaway]

Welcome to the world of trying to proove FM to other IT folks... lol.

Also regardless of whether its Access or FileMaker, creating a view would be more efficient as they should know.

John, good news! I talked with our DBA and he was surprising open to adding a view to the database in question. So I submitted a request.

And thanks everyone for the help and the suggestion about a view. I knew what they were but I never thought about that as the solution.