Prev: Datenbank Design Tool gesucht
Next: oracle forms on win 2003 terminal server
From: jodleren on 27 Apr 2010 11:49
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


From: Carlos on 28 Apr 2010 03:12
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?

Cheers.

Carlos.
From: jodleren on 28 Apr 2010 04:48
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?

From: Carlos on 28 Apr 2010 05:29
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 ?

Cheers.

Carlos.
From: jodleren on 28 Apr 2010 09:56
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
 |  Next  |  Last
Pages: 1 2
Prev: Datenbank Design Tool gesucht
Next: oracle forms on win 2003 terminal server