Re: SQL recursive multiply

From: <james_at_france-mail.com>
Date: Mon, 01 Feb 1999 11:54:48 GMT
Message-ID: <7944m6$n7f$1@nnrp1.dejanews.com>

In article <36B1DCBA.F522D80E_at_capgemini.co.uk>,   Mike Burden <michael.burden_at_capgemini.co.uk> wrote:
> 1) Use exp(sum(ln(qte)))
>
> 2) You can use prior in a start with/connect to retrieve the value of the
> parent row.
>
> e.g. prior qte * qte
>
> although this does not quite answer you question because the rule changes
> depending on the level according to your example. You could use decode to
> return the appropriate value depending on the current level.
>
> Why do you want to do this? It's not just a test is it.
>
> james_at_france-mail.com wrote:
>
> > Hi,
> >
> > Let s assume, I have the following table :
> >
> > PID ID QTE
> > --- -- ---
> > 10 20 3
> > 20 31 2
> > 20 32 3
> > 10 21 2
> > 21 33 5
> > 10 23 1
> >
> > The first question is : how to multiply the QTE column, the same
> > way I can do a simple sum(QTE), to get : 3*2*3*2*5*1=180
> >
> > The second question is : if I consider the following three build
> > with a "start with/connect by" function where PID=parent Identifier
> > and ID=Identifier, how can I recursivly multiply the quantities (QTE)
> > to get the quantity I need for each lowest level :
> >
> > 10
> > 20
> > 31 (3*2=6)
> > 32 (3*3=9)
> > 21
> > 33 (2*5=10)
> > 23 (1=1)
> >
> > Regards, Remy James.
> >

Hi,

 Thank you for the first answer, I should have thought of it, because I have the mathematic competences to find that.

  My question is not a test, the problem is the following : let s assume you have to build a complex object (10) composed of basic (31,32,33) and intermediar (20,21,23) objects, a three in an Oracle database indicates the composition of the complex object using basic and intermediar objects, and the quantities you need for each of these objects.

Now the question is : how many basic object do you need to build your complex object ?
It can be also intersting to have the quantities at each level of the three.

If I only have two level, as given by my example, it is working using a prior on the quantity. Things are in fact deeper in level and basic objects are not necessary at the same level :

10 [C](1)

   20 [I](3*(1)=3)

     31 [B](2*(3*1)=6)
     32 [B](3*(3*1)=9)
   21 [I](2*(1)=2)
     33 [I](5*(2*1)=10)
       40 [B](3*(5*2*1)=30)

   23 [B](1*(1)=1)

Where :
 [C]=Complex object at the top level (10),  [B]=Basic object at a bottom level

    (it doesn t go deeper) (23,31,32,40),  [I]=Intermediar object between [B] and [C] objects (20,21,33).

Regards, Remy James.

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Feb 01 1999 - 05:54:48 CST