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.
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
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
select ord.item, sum(ord.qty), sum(invt.qty)
from ord, invt
group by ord.item
my results are
ITEM ORD.QTY INVT.QTY DIFFERENCE
ITEM1 10 30 20
ITEM2 10 16 6
You see my dilema. Anyone have a suggestion?