Detail OracleDataSet with a Oracle View

[greg]

I'm using a form with a master & detail grid on the form. The master and detail work just fine when I use 2 oracle tables. However, I need to use a Oracle view I created as one of the detail tables.
Can I use a detail OracleDataSet with a view? When I try to do this automatically I get the error message--"Automatic Master/Detail is only possible if a foreign key constraint exists between the Master and the detail table."
Is there a way to do this manually and still use a OracleDataSet so I can use a Data-Aware Grid to view the data?
I'm converting an app from the BDE to DOA, because I've told management how much better DOA is. Please help.

Thanks in advance.

 

[Marco Kalter]

The error message is actually an informational message, and implies that you need to manually set up the master detail relation. Let's assume that you have a master dataset with a field named "id" as the key field, and that the detail dataset has a corresponding "master_id" field. To set up the master/detail relation, perform the following actions in the detail dataset:

• Enter ID in the MasterFields property
  
• Enter MASTER_ID in the DetailFields property
  
• Include "where MASTER_ID = :MASTER_ID" in the where clause of the SQL statement
  
• Define the :MASTER_ID variable

After this, master/detail synchronization should work correctly, regardless of the absence of a foreign key constraint.

------------------
Marco Kalter
Allround Automations

[This message has been edited by mkalter (edited 24 December 1999).]

 

[greg]

Thanks Marco,
Actually, the tables we are using have different field names for the same field in different tables--for example field name "manager" in employee table and "name_manager" in the name table. This is due to poor database design, but we have to use the tables as they are.

It seems that DOA assumes that when you do a join "automatically" the field names in the two tables you are joining should be the same, if they are not you must create an alias for the column name in the sql statement and then the automatic join will work.

That's what I used to solve my problem. Thanks for your help.

 

[Marco Kalter]

It is not necessary that the fields have the same name, most of the time this will not be the case. In my example, the ID field of the master table does not have the same name as the MASTER_ID field of the detail table. The only requirement is that the variable in the where clause and the detail field have the same name (e.g. where master_id = :master_id).

------------------
Marco Kalter
Allround Automations