Updating dataset based upon join

[FastJavelin]

My problem is basically as follows:
I have a Delphi form with 2 DBEdit fields;
I will refer to them here by DBEditX and DBEditY.

The fields are bound to a dataset with this SQL statement:

SELECT T1.RowID, T1.X AS X, NVL(T2.Y, T1.Y) AS Y
FROM T1, T2
WHERE T1.FK = T2.PK (+)

(1)
If T1.FK is NULL then T2.Y is NULL
and the controls show information from T1 only.

(2)
If T1.FK is not NULL then T2.Y is not NULL
and DBEditY shows a value from T2.

What I want to do is this:

Case (1):
Editing the value in DbEditY should update T1.Y;
this works fine.

Case (2):
Editing the value in DbEditY is not allowed so DbEditY is disabled.
However: when I edit DbEditX, I get an error message "record has been modified by another user."
This error occurs on each attempt to put the dataset in Edit mode.

I had expected this to work since I set the dataset's UpdatingTable = T1.
I cannot make Y a calculated field as this would not allow me to update it in case (1).

Is there an elegant way to solve this?

Kind regards,
Geert.

 

[Marco Kalter]

The "record has been modified by another user" error is caused by the fact that the "Y" field in the query is not the same as the "Y" column of T1. These 2 values are compared, and because they are different the error is raised.

You could query T1.Y and T2.Y without assigning them to a data-aware control, add a control that represents the NVL(T2.Y, T1.Y) value, and modify the hidden T1.Y field when the NVL value is changed. The dataset will then resolve the changes correctly.