|
midrange.com
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.
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.
