I am using VFP 6.0 to retrieve data (read-only) from an Oracle Server. I am new at this this the company has recently changed to a different MRP system that uses an Oracle database. I am trying to define a view (or a query) that will allow me to extract the data of interest in order to bypass the original GUI (Visual Manufacturing). I am working exclusively with the View/Query designer. The connection is being established succesfully using an ODBC Oracle driver (provided by Oracle).

I have two major problems that I can't solve so far:

1. If two different tables use the same name for one of the fields, I can define the view and run it once; after that, if I try to modify the design, I get an error message "SQL column not found", although the select statement seems to be using the name of table.field syntax to refer to the fields. Any idea on how I can fix this problem?

2. If am trying to establish relations by drag and dropping, they do not appear in the Join tab but in the Filter tab instead and I can't define the type of relation I want... it also puts a <-> sign next to the filter condition (I am interested in a Left Outer Join type). This makes me loose records from the very first table that do not have matches in the related tables (I definitely can't do this !!!). If I am trying to define the relationships manually in the Join tab, I can choose the type but I can't establish multiple Left Outer Join relationships between the same tables (I can do this with Access 2000 though, I have tried it ... but Access is so slow compared with Foxpro :) )... I have also noticed that the functions set available by using this driver has changed...:(

Can anybody shed some light on how I can make this connection to Oracle work? I can view all the tables, filter them, establish very basic relationships but when it comes to complex ones... it kills me :)

Any help or pointer to the right direction would be very appreciated...

Paul