From a recent email:
- IV00101 is the Item Master table and Item Classes are kept here (ITMCLSCD = field name)
- IV00102 is the quantities table where our on hand, allocated, etc. figures are kept; Item Classes are not in here but they share ITEMNMBR as a key field
I want to find all of the inventory items in IV00102 with quantities on hand > 0 that belong to our Item Class ID 'NEW PARTS'...I probably need to join the tables in SQL somehow on ITEMNMBR so as to query only the ones I'm looking for.
======================
select
i.itemnmbr, rcrdtype,iq.locncode, qtyonhnd
from iv00101 i
join iv00102 iq on i.itemnmbr = iq.itemnmbr
--where iq.rcrdtype = 1
order by i.itemnmbr, iq.rcrdtype, iq.locncode
The IV00102 has a record type field, the '1' type is a summary of all locations, the '2' type is for the individual locations. You'll need to uncomment the 'where' clause, and make it say either 1 or 2, based on your needs