Wiki Home

Updating Outer Join Views


Namespace: SoftwareEng
This post is in response to something that came up in another thread. I mentioned I had done this, so Carl asked me to post some information about updating base tables from an outer joined View.

First, some caveats

The example I'm using is pretty simple. It uses two tables, and only one gets updated. I'm not sure I'd try updating more than one table even via an inner join. Simple as it is, I think it has pretty wide application. Also, DELETEs have to be handled with care.

Why on earth am I doing it this way? What is the problem I'm trying to solve?

The two tables involved are Dept and Function. A function is a grouping of departments, but it exists whether or not there are departments to handle it. So my outer join is all functions plus any departments that have been set up to fill those functions. The fields in my SELECT are:

Dept.iID
Dept.FuncID
Dept.Dept
Function.Function
NVL(Dept.Dept,"") AS DispDept (for display purposes in the grid)

This way I can add departments that are specific to a client to address a more-or-less fixed set of needs (Functions), but no one client has to have the same reporting structure as another.

How does it look?

I don't particularly like the user interface that has data entry fields on one page of a pageframe, and an overview grid on another page. I find it cumbersome to use, switching back and forth between pages. And I find it cumbersome to develop, as it complicates the containership hierarchy. So instead, I'm putting a grid right in the Presentation container with the data entry fields. When the user fills in the data entry fields and Saves, the new record goes right into the grid for them to see.

In this case the grid just has two fields, Function and Dept... actually, DispDept, so I can let SQL do the translation from NULL to Blank when it gathers the data, and the GETFLDSTATE() doesn't change.

How do you update the View?

If you're just adding a whole new record, the update process is the same as with any other View, where TABLEUPDATE() of the View commits the new record to the base table, as expected.

There's a catch when you update one of the existing records that is half filled in with NULLS. First off, you have to be able to populate the PK field (this was the original problem I experienced with VFE... I couldn't figure out a way to do that within the framework). But assuming you can REPLACE PKField WITH <somevalue> and then also fill any other relevant fields, when you go to TABLEUPDATE() the View, VFP throws in a curve ball.

VFP doesn't accomplish the UPDATE in a single step. It first DELETEs the record(s) from the View by setting the delete flag. It doesn't pass it down to the base table yet, since this record never existed in the base table being updated! In order to get the new information into the base table, you have to RECALL the deletion(s) in the View, and issue the TABLEUPDATE() against the View again. Now the new information gets added as new records to the base table.

Note that this two-step process has nothing to do with choosing "SQL UPDATE" over "SQL DELETE then INSERT". This is the way it happens when you have SQL UPDATE selected for the View.

Sorry for the length... I hope this is clear.

Contributors: Ceil Silver
Category Data
( Topic last updated: 1999.10.12 07:04:43 PM )