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

Loyd, thanks for yanking the right words out; using a UDF is what I meant.
I've looked long and hard at a way to do this with the DDS-style database
but haven't come up with anything even vaguely useable.  If SQL and a UDF
can't do it, then it's off to code QLGSORT (which has some benefits in this
application).

Reeve specifically mentioned SQL....
It is certainly possible to code a (RPG) program to perform the calculation.
As you say, there probably is one already on the system. The problem comes
in play that the program is not usable in SQL queries, unless you populate a
work file, then get the price, etc. Although I haven't had the opportunity
to do so, I imagine a RPG program could be coded to do the price lookup,
*and* serve as a stored procedure or user defined function in SQL. You can
create RPG stored procedures. But I think what Reeve is after is a user
defined function. I haven't explored if/how to code a UDF in RPG.

I can imagine using files to store the price, but with so many combinations,
it would become unwieldy. Reeve didn't say, but if there are multiple
incentives, or if they are vendor or customer specific, the price
calculation would be a hairy process indeed. A UDF or price program sounds
like the way to go.

JMHO,
Loyd


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


Loyd,

These are all excellent points, and they are the reasons I generally try to
normalize the DB.

I do not know the trucking industry, but I'm guessing it's pretty similar to
the retail industry.  In retail, events which change the current price
(changes to base price or customer incentives) are infrequent.  I believe
that maintaining a history of prices over time can be handled a number of
ways, and is best treated as a separate issue.

This is a case where I'd base the decision, not so much on pure
normalization rules, but on how useful it would be to have the current price
handily available at all times.  If Reeve was strictly talking about 1
screen, I sure wouldn't go to the trouble of putting it in the DB.  But I've
found that things as fundamental to the business as current price /would/ be
used quite frequently, if it was readily available.  IOW, I think it's use
would expand, if it was available.

<Just saw Reeve's post.>

I guess I'm not seeing where the problem is, with the numbers of
combinations that make up price.  I see the difficulty in the calculation,
and am not trying to minimize that.

But IOW, how would you create the function is SQL?  And why couldn't this
same concept be implemented in RPG?  If it's possible to code it in SQL, I
would think you could also code it in RPG.  I would also think this code
already exists in the system someplace, currently, and it's probably in RPG
already.  Or am I missing something?  (Most probably answer..: the
latter...;-)

jt

> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of Goodbar, Loyd
> (AFS-Water Valley)
> Sent: Tuesday, November 13, 2001 9:12 AM
> To: 'midrange-l@midrange.com'
> Subject: RE: SQL/ordering data based on a computed result/stored
> procedure?
>
>
> 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.
> _______________________________________________
> 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.
_______________________________________________
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.