Query with summed join?

I have a query that is giving me issues with regards to getting a running total everytime new inventory is added. I have 2 tables, stock_receiving and stock_items. Stock receiving is where is record is listed when a part is received for a particular contract (division of our company). It referenced by rcv_id. Stock_items is where all of the current quantities are of each part by contract.  Stock items can have the same part listed multiple times based on the number of divisions that use the part and each location that each division warehouses them at.  Example contract A can have stock number 1234 in location 1 and 2 and contract B can have stock 1234 in location 2 and 3. Even though the same part is in location 2 the quanities have to be kept separate as they belong to two different groups. We do this by having 4 entries in stock items: "stock1234 for division A in location 1", "stock1234 for division A in location 2", "stock1234 for division B in location 2",  and "stock1234 for division B in location 3", each with their respective quantities.  When the item is received an entry goes in the stock_receiving table for that receipt and the qty in stock_items is increased based on the location, division and stock_number so we know how much of each part, at each location, each division has.

When we are receiving it is based solely on the division. So if division A wants to receive STK1234 with a QTY of 100 in location 1, I need to show the total qty now available to division 1 from all of their locations in a column on that receiving screen.


Here is what I use to pull the records on the receiving document with id 12357:

SELECT stock_receiving.STOCK_NO, stock_receiving.PART_NO, stock_receiving.QTY, stock_receiving.CONTRACT_ID, stock_receiving.RCV_ID
FROM STOCK_RECEIVING
WHERE stock_receiving.RCV_ID=12357

Here is what I came up with to pull the total qty of stock 8167 for division 331

SELECT sum(stock_items.qty) as totalqty, stock_items.stock_no
FROM STOCK_ITEMS
WHERE stock_no = 'STK8167' and contract_id = '331'
group by stock_no

They both give me the correct results but I can't figure out how to tie them together in a single query to give me the running tally on the receiving screen each time I add an entry.
LVL 1
SchuchertAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SujithData ArchitectCommented:
Assuming that stock_no and contract_id can be used to join these two tables.
Try this query.
SELECT SR.STOCK_NO, SR.PART_NO, SR.QTY, SR.CONTRACT_ID, SR.RCV_ID, sum(totalqty) over(partition by X.stock_no) tqty
FROM STOCK_RECEIVING SR, 
( SELECT sum(stock_items.qty) as totalqty, stock_items.stock_no
FROM STOCK_ITEMS
group by stock_no, contract_id ) X
WHERE SR.stock_no = X.stock_no
and SR.CONTRACT_ID = X.CONTRACT_ID
/

Open in new window

0
SchuchertAuthor Commented:
I get "ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X' "
0
SujithData ArchitectCommented:
what is OGC_X?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SchuchertAuthor Commented:
I'm not sure. I googled it and see other people have asked the same thing. I'm still trying to find a real answer to that.
0
dqmqCommented:
SELECT stock_receiving.STOCK_NO, stock_receiving.PART_NO, stock_receiving.QTY, stock_receiving.CONTRACT_ID, stock_receiving.RCV_ID,
sum(stock_items.qty) as totalqty
FROM STOCK_RECEIVING
inner join STOCK_ITEMS on STOCK_RECEIVING.STOCK_NO=STOCK_ITEMS.STOCK_NO

--not sure which where clause you want.  One or the other
--WHERE stock_receiving.RCV_ID=12357
WHERE stock_no = 'STK8167' and contract_id = '331'

GROUP BY
stock_receiving.STOCK_NO, stock_receiving.PART_NO, stock_receiving.QTY, stock_receiving.CONTRACT_ID, stock_receiving.RCV_ID









0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SchuchertAuthor Commented:
It's by rcv_id and it seems to work.

Thanks dgmg for your quick reply.

Thanks also sujith80 for your help but dgmg's solution worked for me.
0
SchuchertAuthor Commented:
It's by rcv_id and it seems to work.
Thanks dgmg for your quick reply.
Thanks also sujith80 for your help but dgmg's solution worked for me.
0
SchuchertAuthor Commented:
Actually, I might have found a snag. Should the join use the contract_id also?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.