Re: Calculated/derived fields

Assuming you need to keep both old (like Query400) and new stuff working, I would use a trigger to keep the extended field updated.
Jim Franz

----- Original Message ----- From: "Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx>
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Sent: Monday, September 08, 2008 3:49 PM
Subject: Calculated/derived fields

All,

We're a food distributor with all 15 standard price brackets stored in
the item master. Been that way since the 70's. The maximum price that
will fit in a bracket is $999.99. Within a couple of years we will need
to have pricing ability above $1000.00. That will be some project for
me because we're talking item master, orders, and all sales files. Not
to mentioned every printed report, display file, invoices, etc where
they appear. I can hardly wait. <g>

When that happens, I think it's time to do some normalization. I'm
going to take all price brackets out of the item master and put them
into a price bracket master, 1 record per item/bracket. I'm going to
take all cost fields (market, last, average, bid, etc) out of the item
master and put them into a cost master.

In 90% of the cases, our standard price brackets do not contain a raw
dollar amount, but a percent (there is a code field that determines
whether the standard brackets are dollar or percent). For example, if a
percent item contains 125.00, that does not mean the price is $125.00,
but is calculated as (115.00 / 100) * COST. If we change the cost
field, all standard price brackets dollar sell are immediately changed,
because I have a single called subprogram that calculates the dollar
amount when an item master record is read and sticks it right back into
the same field for subsequent use (within the program - the database
field is not changed).

First off, I'm thinking the price bracket master will look something
like this:

ITNBR 6A Item number (part of key)
IBRKT 1A Bracket (part of key)
PCECD 1A Dollar/Percent code (a P or D)
ITPRC7 7,2P Price pct or dollar amt, based on PCECD

ITNBR / IBRKT constitute the primary key.

Something CRPence posted last week about SQL derived fields in V6R1 got
me to thinking. Suppose there was an additional field in this file:

ITDLR7 7,2P Actual dollar sell

where, if PCECD was a D, this field would be the same as ITPRC7. If
PCECD was a P, this field would contain the result of this calculation:
(ITPRC7 / 100) * COST. This would save application programs from having
to calculate it upon virtually every read, as well as users not having
to calculate it in Queries. IOW, have the database maintain it.

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?

--
Jeff Crosby
UniPro Foodservice/Dilgard
260-422-7531
Opinions expressed are my own and not necessarily those of my company. Unless I say so.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.