Hello,
I'm looking for some assistance on how to solve a looping issue with a query. I'm trying to create a list of all products, the total quantity in inventory for all warehouses, max invoice date and max PO receipt date regardless of which warehouse it occurred in. I'm creating a temp table to get all the unique product numbers and then doing a for each on the ICSW table. My problem is I need to limit which warehouses I query against as there are some that need to be excluded if their ecommecerty flag is not equal to "Y". If I query ICSD before ICSW then the inventory quantity being calculated is a multiple of the number of warehouses which isn't correct. The whse field is character. I also tried doing a can-do on the for each icsw for the whse field but got zero results which is not correct. Any suggestions on how to solve this?
I have two tables:
ICSW -this table contains all product information for products setup within the warehouse
ICSD - this contains all of the warehouses for a company
ICSW has a unique index of cono, whse, product.
I'm looking for some assistance on how to solve a looping issue with a query. I'm trying to create a list of all products, the total quantity in inventory for all warehouses, max invoice date and max PO receipt date regardless of which warehouse it occurred in. I'm creating a temp table to get all the unique product numbers and then doing a for each on the ICSW table. My problem is I need to limit which warehouses I query against as there are some that need to be excluded if their ecommecerty flag is not equal to "Y". If I query ICSD before ICSW then the inventory quantity being calculated is a multiple of the number of warehouses which isn't correct. The whse field is character. I also tried doing a can-do on the for each icsw for the whse field but got zero results which is not correct. Any suggestions on how to solve this?
I have two tables:
ICSW -this table contains all product information for products setup within the warehouse
ICSD - this contains all of the warehouses for a company
ICSW has a unique index of cono, whse, product.
Code:
def temp-table tt-stale-inv
field mfgno like icsp.prod
field descrip like icsp.descrip3
field qtyavail like icsw.qtyonhand init 0
field vendor like apsv.name
field listprice like icsw.listprice init 0
field lastrcptdt like icsw.lastrcptdt
field lastinvoice like icsw.lastinvdt
field monthsold as int
index tk-staleinv is primary unique
mfgno.
for each icsp where
icsp.cono = 10 and
icsp.statustype = 'A'
no-lock:
find tt-stale-inv where
tt-stale-inv.mfgno = icsp.prod
exclusive-lock no-error.
if not avail tt-stale-inv then do:
create tt-stale-inv.
assign
tt-stale-inv.mfgno = icsp.prod
tt-stale-inv.descrip = string(icsp.descrip[1] + " " + icsp.descrip[2]).
end.
end.
for each icsw where
icsw.cono = 10 and
can-find(first icsd where icsw.cono = icsw.cono and icsd.whse = icsw.whse and icsd.ecommercety = 'y' no-lock)
no-lock:
Last edited by a moderator: