Re: Use of SP?

On Dec 18, 12:12 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Dec 17, 9:28 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > 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
>
> The one I'm working on now is about 10K lines of proprietary 4GL code
> (some of which explodes into more OCI SQL, some of which runs on an
> application level, some of which are blank/continued lines [from using
> wc -l to count mindlessly]). I will be upgrading this soon as part of
> one of those death marches, the new version is much more "integrated,"
> so the customizations will be that much more difficult. I've got a
> hack almost working that uses the old code and ignores the new that
> conflicts, which will at least get it going but will likely have at
> least some issues blow up in our collective faces - hopefully less
> noticeably than some of the issues from moving functional targets.
>
> Yes, I'm just talking about price lists, this is in addition to the
> sales order system. Not particularly complex, as these things go.
> Does give me a different perspective when I see "Buy One, Get One
> Free" on a store display, not exactly the reaction the marketeers
> expect, I'm sure.
>
> jg
> --
> @home.com is bogus.http://www.computerworld.com/action/article.do?command=viewArticleBas...- Hide quoted text -
>
> - Show quoted text -

Rarely is price structuring simple except, possibly, for the lemonade stand run by the 6-year-old next-door neighbor who's charging the exact same rate for everyone ... except his friends, who pay less ... oh, and Daddy and Mommy, who aren't charged but pay anyway ... and oh, yeah, the neighbor kid who is the local bully who pays much more than anyone else ... oh, and Aunt Ruby who visits occasionally and who gets the Senior Citizen discount ... and, oops, the mail lady who gets lemonade for free because she's nice and delivers presents on occasion ...

And that's just the local lemonade stand.

Real-world price lists can be brutal, I agree. They're almost worse than the U.S. Income Tax Code. Almost.

You have my respect, my admiration and my deepest sympathies.

David Fitzjarrell Received on Tue Dec 18 2007 - 14:27:09 CST