× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.