Re: Use of SP?

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