SQL Query
Date: 23 Apr 2003 09:19:55 -0700
Message-ID: <1ec5f288.0304230819.7d3bd411_at_posting.google.com>
Now that I have a flat spot on my head,(from beating my head against
the monitor) I will post my question.
The company I am working with has a database that they can not
control. The software they use to interface with oracle (7.*.*)
requires that some "things" remain the way they are. No views, no
additional tables, no database links, etc. etc.
Here's my problem:
I want to know what items on order have either no inventory or the
inventory available is not enough. The inventory table has a feature
of storing each item with the lot code as a seperate column. Here is
some sample data.
As you can see if I total the INVT for ITEM1 = 15 and ITEM2 = 8.
If I total the ORD for ITEM1 = 10 and ITEM2 = 10.
Then subtracting the difference I should see ITEM1 +5 ITEM2 -2.
But with sql when I run a query of
my results are
TABLE INVT TABLE ORD
LOT_CODE ITEM QTY ITEM ORD QTY
======================== =========================
LOT1 ITEM1 0 ITEM1 12345 5
LOT2 ITEM1 10 ITEM1 98765 5
LOT3 ITEM1 5 ITEM2 12345 5
LOT1 ITEM2 5 ITEM2 98765 5
LOT5 ITEM2 3
LOT4 ITEM2 0
select ord.item, sum(ord.qty), sum(invt.qty)
,(sum(ord.qty)-sum(invt.qty))
from ord, invt
group by ord.item
ITEM ORD.QTY INVT.QTY DIFFERENCE
ITEM1 10 30 20
ITEM2 10 16 6
You see my dilema. Anyone have a suggestion? Received on Wed Apr 23 2003 - 18:19:55 CEST