RE: Calculated/derived fields

No real issues with what you propose. One thing database purists go on
about is "do not store calculated fields in a table". Just as you
describe, keeping itdlr7 correct can be difficult. In my opinion, the
best way is to not store itdlr7 at all in the table. Instead, store it
in a view. The view can carry over itnbr, ibrkt, pcecd and itprc7.
Calculate the itdlr7 as part of the view. Since the view carries over
the same columns as the base table, it will share the same indexes if
selects are the same (by itnbr and ibrkt).

Example logic:

Create view blah as
Select itnbr, ibrkt, pcecd, itprc7, cost,
Cast(Case when pcecd = 'D' then itprc7
Else itprc7 / 100 * cost
End as decimal(9,2)) as itdlr7

Now you never need to store the actual dollar amount, and it is always
kept up to date.

HTH,
Loyd

Loyd Goodbar
Business Systems
BorgWarner Shared Services
662-473-5713

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jeff Crosby
Sent: Monday, September 08, 2008 2:49 PM
To: Midrange Systems Technical Discussion
Subject: Calculated/derived fields


So the question becomes, how does one keep ITDLR7 maintained at all
times? Note that ITDLR7 should change when any of the following fields
change: PCECD, ITPRC7, or COST. And just to make it interesting, COST
will be in another file altogether.

Can a trigger program handle this, and not bring the system to it's
knees? Note that there would have to be triggers on both the price
bracket file _and_ the cost file to handle this.

What about an SQL view? Can a view reference another file? The problem

is a view has no key fields, right? HLL access via key is still
required.

This is not an imminent thing, I just need to start getting ducks in a
row.

Am I off my rocker even thinking about this?