Hello,

I am an absolute beginner in ORACLE, and I have the following problem
- I'll try to explain it as clearly as possible. I'm working on an
existing form (in VB) which uses an fpGrid (bound to a table, say,
Employees) through an Oracle Data Control. This form allows editing,
adding, deleting, updating of the Employee's details. I have been
asked to 'upgrade' the form to allow for sorting (on the event
Head_click of the grid). The problem is the following:

The table the grid is based on (bound to) is table Employees. It
contains columns like EmpID, Dept ID. The grid, though, contains the
columns (EmpID, First Name, Last Name, Address, DeptID, Dept Name,
Dept Code). The columns (EmpID, DeptID) are invisible (and they are
the columns the grid in bound on). The visible columns (First Name,
Last Name, Address) come from the table EmployeeDetails, (Dept Name,
Dept Code) from DeptDetails, both referenced by Employees. These
details are obtained by using LOOKUPS on the event RowColChange in the
grid (I know, it's not efficient, but that's the way it has been made
years ago).

NOTE that the details are NOT editable per se, just selectable (combo
boxes). So all I need to store with this grid is a record in Employees
- for instance, I could assign an Employee to a different Dept, (which
I have selected from a combo in the grid, using either the column Dept
Name or Dept Code ) - in this example, I'd have to update the DeptID
column in the Employees table, for that respective employee.

Now I can't rebuild my query with an ORDER BY clause and then refresh
the grid, because the column in the 'order by' clause does not exist
in the table Employee, only in either EmpDetails or DeptDetails. So
normally, this setup will only allow for sorting on EmpID or DeptID
(if they were visible, of course).

The form is VERY complicated, and rebuilding it completely (to make it
unbound, and have complete control) would take too long, acording to
my boss. I have tried to create a VIEW containing all the columns in
Employees (invisible in the grid) and the relevant (sortable, visible)
columns in EmpDetails & DeptDetails. Of course, the columns (EmpID
and DeptID) in the main table are UPDATABLE, the rest (in the details
table) are NOT. That would be fine with me (see NOTE above).

So after some digging, I succeeded to make it work for UPDATES and
DELETES, (and, yes, you can sort correctly on any column now). But my
INSERTS still don't work - I get "ORA-01776 Cannot modify more than
one base table through a join view". Still, WHY DO MY UPDATES WORK ???

So my questions are:
1. What can I do to solve this problem with the INSERTS ? (Should I
use an INSTEAD OF trigger ? Isn't this too complicated for the problem
at hand ?)
2. Is there any other simpler way to do this sorting (other than using
the VIEW) ?

Thanks a lot for any advice - I'm alone on this one, and I have to
come up with something pretty quick !

Alex.