× 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.


  1.  Home
  2. MIDRANGE-L
  3. November 2001

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

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