Re: select last time for ordering - complex sql

From: Carlos <miotromailcarlos_at_netscape.net>
Date: Wed, 28 Apr 2010 07:51:59 -0700 (PDT)
Message-ID: <0c60bace-5dbb-4482-a1e6-cf04fc3d03f5_at_g30g2000yqc.googlegroups.com>



On Apr 28, 3:56 pm, jodleren <sonn..._at_hot.ee> wrote:
> On Apr 28, 12:29 pm, Carlos <miotromailcar..._at_netscape.net> wrote:
>
>
>
> > On Apr 28, 10:48 am, jodleren <sonn..._at_hot.ee> wrote:
>
> > > On Apr 28, 10:12 am, Carlos <miotromailcar..._at_netscape.net> wrote:
>
> > > > On Apr 27, 5:49 pm, jodleren <sonn..._at_hot.ee> wrote:
>
> > > > > Hi all
>
> > > > > Basically I have this, which gives me a tree of the product and
> > > > > subasseblies.
>
> > > > > SELECT level, stuff from products
> > > > > CONNECT BY PRIOR products.subpart = products.product
> > > > > START WITH products = 'productname'
>
> > > > > Now, they want to know delevery times for the last time when the parts
> > > > > were ordered, this goes like:
>
> > > > > select ordertable.orderdata, delirverytable.deliverydate
> > > > > from ordertable, delirverytable
> > > > > where ordertable.orderno = delirverytable.orderno
> > > > > and delirverytable.product='Something'
> > > > > and delivery date in (select max(delirverytable2.deliverydate) from
> > > > > delirverytable2 where
> > > > > delirverytable2.product = delirverytable.product)
>
> > > > > basically - I get the latest date for delivery (if present) in order
> > > > > to get one row only.
>
> > > > > Now, can I mix all this together?
> > > > > The point is, that some parts might never have been ordered, so they
> > > > > are not in - at least one of - the order and delivery tables.
>
> > > > > WBR
> > > > > Sonncih
>
> > > > "The point is, that some parts might never have been ordered, so they
> > > > are not in - at least one of - the order and delivery tables."
>
> > > > OUTER JOIN?
>
> > > But with 3 queries mixed?
>
> > > That is where I get lost?
>
> > (RECURSIVE SELECT) OUTER JOIN (ORDER/DELIVERY SELECT) ON PRODUCT ?
>
> I can send you an email about it, then you can get a look at it. As of
> now, it does not work....
>
> Note:
>
> select product tree
> select latest only data from table deliveries (if present)
> select order data from order table
>
> this comes to:
>
> SELECT level, stuff ,
>  ordertable.orderdata, delirverytable.deliverydate
> FROM products
> LEFT OUTER JOIN delirverytable ON
> delirverytable.product=products.something
>    AND delirverytable.deliverydate in (SELECT MAX(d2.deliverydate)
> delirverytable d2 where d2.product=products.something)
> CONNECT BY PRIOR products.subpart = products.product
> START WITH products = 'productname'
>
> problem: I need the MAX in order to get only the latest from from
> delivery -
> it works without the MAX, but then I get a long list of rows when it
> was delivered - btw it is sloooooow
>
> Also, I need to get data from "product_details"
>
> with the max I get this erorr:
> [Oracle][ODBC][Ora]ORA-01799: a column may not be outer-joined to a
> subquery
>
> ----------------------
> next we need the order date:
> this is not tested, and I am not even sure how to take the order table
> into it...
>
> SELECT level, stuff ,
>  ordertable.orderdata, delirverytable.deliverydate
> FROM products
> LEFT OUTER JOIN delirverytable ON
> delirverytable.product=products.something
>    AND delirverytable.deliverydate in (SELECT MAX(d2.deliverydate)
> delirverytable d2 where d2.product=products.something)
> LEFT OUTER JOIN ordertable ON ordertable.orderno =
> delirverytable.orderno
> CONNECT BY PRIOR products.subpart = products.product
> START WITH products = 'productname'
>
> but I never get this far.
>
> I wonder whether to do it in code - that might be both easier and
> faster
>
> WBR
> Sonnich

First of all, your second query (ordertable/delirverytable) is uuuglyyy. Also, you likely would like to use some 'WITH' refactoring...

But something like this maybe will do the homework OK

SELECT *
  FROM ( SELECT LEVEL,

                STUFF,
                PRODUCT
           FROM PRODUCTS
        CONNECT BY PRIOR SUBPART = PRODUCT
          START WITH PRODUCTS = 'productname' ) rec
  LEFT OUTER JOIN ( select ordertable.orderdata,
                           delirverytable.deliverydate,
                           delirverytable.product
                      from ordertable,
                           delirverytable
                     where ordertable.orderno = delirverytable.orderno
                       and delirverytable.product='Something'
                       and delivery date in (select
max(delirverytable2.deliverydate) from delirverytable2 where delirverytable2.product = delirverytable.product)   ) ord
    ON (rec.PRODUCT = ord.PRODUCT)

Next time, ask the teacher ;-)

Cheers.

Carlos. Received on Wed Apr 28 2010 - 09:51:59 CDT

Original text of this message