Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: 2nd try
On Fri, 05 Nov 2004 15:53:55 -0500, Patrice Béliveau
<pbel_at_hotmail.com> wrote:
>Hi,
>
>I have two table, one is containing a dated list of delivery to do and
>the second contain a list of delivery done.
>
>ex: Table 1 --> Order No,
> Delivery date requested,
> Quantity requested
>
> Table 2 --> Shipping No.
> Order No.
> Date shipped
> Quantity shipped
>
>
>They are not into the same list because there is no one on one
>relation between those two list (ex: some delivery might have been
>back order, or that might have been some over shipment, etc...)
>
>The QUESTION:
>
>what query can I do to known where am I at in my delivery schedule?
>
>
>What I would like is something like a list with
> Delivery date requested
> Quantity requested
> Quantity shipped
>
>The only that I can see of how to do it, it's to write a function that
>will figure out how much have been shipped for each delivery, but this
>cause heavy overhead.
>
>Another way of looking at it would be:
>
>- Give me all Order No, where SUM(quantity requested) > SUM(quantity
>shipped).
>
>but then sum on the left side of a test is not permitted
>
>So, I'm stuck, I do not know how to proceed
>
>Thanks
To make thing simplier:
Table 1: Order_No (PK) Delivery_date (PK) Quantity Table 2: Shipping_no (PK) Order_no Quantity
I want to find out Order_no where the sum of all quantity in table_1 of the same Order_no is greater than the sum of all quantity in table_2 of the same Order_no
I know that I cannot do that, but it would look like
SELECT T1.ORDER_NO FROM TABLE_1 T1 WHERE (SELECT SUM(T2.QUANTITY) FROM TABLE_1 T2 WHERE T1.ORDER_NO=T2.ORDER_NO) > (SELECT SUM(T3.QUANTITY) FROM TABLE_2 T3 WHERE T1.ORDER_NO=T3.ORDER_NO);Received on Tue Nov 09 2004 - 09:53:44 CST
![]() |
![]() |