Re: Use of SP?

On Dec 17, 11:10 am, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 17, 8:49 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Dec 17, 2:58 am, "Hassi" <ha..._at_nospam.com> wrote:
>
> > > Hi!
>
> > > In our system the user can work offline by synchorinzing the server database
> > > to a local Oracle Express (Oracle Express 10g on windows platform) database.
> > > I have one table (call it pricelist) that contains salesparts with pricing
> > > info. In the system a user can search for a salesparts number and based on
> > > several criterias the correct price should be fetched from the pricelist
> > > table.
>
> > > 1. If condition A, B, C and D are satisfied fetch that row from the
> > > pricelist table.
> > > 2. If condition A, B,C and E are satisfied fetch that row from the
> > > pricelist table.
> > > 3. If condtion A,B,C are satisfied fetch that row from the pricelist table.
> > > 4. If condtion A, C, F are satisfied fetch that row from the pricelist
> > > table.
> > > 5. Finally fetch baseprice which always exist
>
> > > I'm not a DBA but my thought for this was to create a stored procedure
> > > returning a ref cursor and send in the input parameters (like product
> > > number, customer number, quantity etc) and then basically execute queries in
> > > number order (I mean following the criterias above) and as soon as the query
> > > returns records I will exit the SP and return the recordset. Is this the way
> > > to go to tackle tasks like this or am I walking towards a dead end?
>
> > > Regards
> > > Hassi
>
> > Why use PL/SQL when SQL*Plus works just as well:
>
> > Create tables --
> ...
>
> > This should get you started in how to write such a query without using
> > PL/SQL. Of course it's not an exhaustive treatise on the subject, and
> > others may devise more elegant solutions to the problem, but you can,
> > and should, do this with regular SQL.
>
> > David Fitzjarrell
>
> For the problem as stated, this is correct. However, my experience
> has been that the particular problem statement for price lists is
> never stable. All it takes is one salesperson dealing with one big
> customer and the algorythms change, sometimes in an extreme manner,
> and often in ways anathema to set theory design. Some commercial
> software made to generalize this solution is just short of insane -
> and the "just short" is only there because it can be made to work.
>
> Time-based attributes always manage to confuse this issue, too.
>
> jg
> --
> @home.com is bogus.
> "Voting on roller coasters is one of the unheralded perks of living in
> a democracy, and something we just don't do often enough." - Gerry
> Braun- Hide quoted text -
>
> - Show quoted text -

I don't expect the problem definition to be stable. My intent was to illustrate, within the scope of the stated constraints, how to avoid using PL/SQL. And I agree that should the problem become far more complex than the OP posted at the outset (which is far more likely than it remaining at the currently simple level) it may well be necessary to use PL/SQL to evaluate all of the possible conditions required to return a 'correct' result.

Of course one might possibly get creative and write a 76,000 line SQL*Plus query ...
<g>

David Fitzjarrell Received on Mon Dec 17 2007 - 11:28:33 CST