RE: SQL/ordering data based on a computed result/stored procedure?

["Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

Database normalization rules frown upon storing computed values in a
database. If the computation changes, all the records must be updated. In
Reeve's case, the entire file might need to be updated every day. Even then,
he would not be able to get prices for dates in the past or future.
Encapsulating the price generation in a function allows him to retrieve any
items price over a wide range of dates, if his incentives file retains old
incentives.

That is one thing I like about SQL - you can create functions to perform
dynamic calculations, and have them available in your programs, (SQL)
queries, etc. without needing to create work files, or perform extra
processing to get the dynamic values.

Loyd

-----Original Message-----
From: jt [mailto:jt@ee.net]
Sent: Tuesday, November 13, 2001 7:58 AM
To: midrange-l@midrange.com
Subject: RE: SQL/ordering data based on a computed result/stored
procedure?


Reeve,

I may be old-fashioned, but I've held to the idea that if you have a need to
sort on a computed field, you should have that in the DB.  I would suggest
triggers to implement the business rules, although there is a performance
hit.

JMHO.

jt

> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of Reeve Fritchman
> Sent: Tuesday, November 13, 2001 8:12 AM
> To: Midrange-L@Midrange. Com
> Subject: SQL/ordering data based on a computed result/stored procedure?
>
>
> This is a multi-part message in MIME format.
> --
> [ Picked text/plain from multipart/alternative ]
> I need to display data in ascending order where the key field (price) is
> dynamically computed.  I can do this with the QLGSORT API's and I
> can do it
> with funky RPG; I'm wondering if I can do it with SQL and stored
> procedures,
> or something of that ilk.
>
> "Price" is a function of the item's base price and the current incentives
> (discounts) in place for that customer/product line combination;
> the actual
> incentive is determined from a complex set of business rules.
> Therefore, a
> $100 item with a 20% discount (net $80) becomes less expensive than a $90
> item with a 10% discount (net price $81), and I'd like the $80
> item to show
> up before the $81 item.
>
> So, SQL reads a group of items, applies a function to the item base price,
> gets a "net" price, and presents ordered-by-net-price data into my
> application program.  It seems simple enough...
>
> No green-screen, client/server, or CFINT opinions are required!
>
> Thanks,
> rf
>
>
> --
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L)
> mailing list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>

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