|
Rob, Sorry... I wasn't intending to imply that OPNQRYF would work, at all, in this particular scenario. Just that it frequently accomplishes (especially when combined with RPG) what SQL does. jt > -----Original Message----- > From: midrange-l-admin@midrange.com > [mailto:midrange-l-admin@midrange.com]On Behalf Of rob@dekko.com > Sent: Tuesday, November 13, 2001 1:41 PM > To: midrange-l@midrange.com > Subject: RE: SQL/ordering data based on a computed result/stored > procedure? > > > > Let me change "I truly believe that OPNQRYF or physically storing > the value > would > work." to I truly believe that OPNQRYF or physically storing the value > would NOT > work. > > Rob Berendt > > ================== > "They that can give up essential liberty to obtain a little temporary > safety deserve neither liberty nor safety." > Benjamin Franklin > > > > rob@dekko.com > Sent by: To: > midrange-l@midrange.com > midrange-l-admin@mi cc: > drange.com Fax to: > Subject: RE: > SQL/ordering data based on a computed result/stored > procedure? > 11/13/2001 12:04 PM > Please respond to > midrange-l > > > > > > > > No. I truly believe that OPNQRYF or physically storing the value would > work. OPNQRYF would not work because it is not complex enough. > Physically > storing the value would not work because of the fact that pricing changes > by time for this application. That is why the > select item, cust, price(item, cust) from thisfile > > works. price is a stored procedure with two parameters: item and cust, > and returns one value, the price. Much like using a subprocedure > in RPGLE. > And your stored procedure can be written in RPGLE, or whatever. From what > I gathered, trying to cobble OPNQRYF to calculate price would > fail. Unless > you processed your OPNQRYF against a SQL view which contained this > price(item,cust). > > Rob Berendt > > ================== > "They that can give up essential liberty to obtain a little temporary > safety deserve neither liberty nor safety." > Benjamin Franklin > > > > "jt" <jt@ee.net> > Sent by: To: > <midrange-l@midrange.com> > midrange-l-admin@mi cc: > drange.com Fax to: > Subject: RE: > SQL/ordering > data based on a computed result/stored > procedure? > 11/13/2001 11:25 AM > Please respond to > midrange-l > > > > > > > Rob, > > This is true also. Or OPNQRYF. > > But depending on the extent it /could/ be accessed, there are performance > gains to physically store the value. I think current price would be > something accessed quite frequently, if it was readily available, > but ICBW. > > Having seen the follow-up posts, I would add: or ILE modules, or UDF, or > /COPY, yada, yada, yada. > > I think I can safely and categorically state: "There is more than one way > to skin a cat"...;-) These kinds of implementation decisions all > come down > to balancing performance, likelihood business rules will change, and > skill-set, IMV. > > jt > > > -----Original Message----- > > From: midrange-l-admin@midrange.com > > [mailto:midrange-l-admin@midrange.com]On Behalf Of rob@dekko.com > > Sent: Tuesday, November 13, 2001 10:16 AM > > To: midrange-l@midrange.com > > Subject: RE: SQL/ordering data based on a computed result/stored > > procedure? > > > > > > > > Whose says you can't store it in the database? Just don't store it > > physically - use a view. > > > > Rob Berendt > > > > ================== > > "They that can give up essential liberty to obtain a little temporary > > safety deserve neither liberty nor safety." > > Benjamin Franklin > > > > > > > > "jt" <jt@ee.net> > > Sent by: To: > > <midrange-l@midrange.com> > > midrange-l-admin@mi cc: > > drange.com Fax to: > > Subject: RE: > > SQL/ordering data based on a computed result/stored > > procedure? > > 11/13/2001 08:57 AM > > Please respond to > > midrange-l > > > > > > > > > > > > > > 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. > > > > > > _______________________________________________ > 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 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.
midrange.com
