Prev: Datenbank Design Tool gesucht
Next: oracle forms on win 2003 terminal server
From: Carlos on 28 Apr 2010 10:51
On Apr 28, 3:56 pm, jodleren <sonn...(a)hot.ee> wrote:
> On Apr 28, 12:29 pm, Carlos <miotromailcar...(a)netscape.net> wrote:
>
>
>
> > On Apr 28, 10:48 am, jodleren <sonn...(a)hot.ee> wrote:
>
> > > On Apr 28, 10:12 am, Carlos <miotromailcar...(a)netscape.net> wrote:
>
> > > > On Apr 27, 5:49 pm, jodleren <sonn...(a)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.

First  |  Prev  | 
Pages: 1 2
Prev: Datenbank Design Tool gesucht
Next: oracle forms on win 2003 terminal server