Help with a query.

35 Views

Hi There,

I've set up a query for a customer so he can run a report over a particular warehouse, showing Item code, Item description, Item Cost and Last Purchase price.

The query I've got is SELECT OITM.ITEMCODE, OITM.ITEMNAME, OITM.AVGPRICE, OITW.WHSCODE,OITM.LASTPURPRC FROM OITM,OITW WHERE OITW.WHSCODE='10'

Firstly, The above query ends with a report showing 6400 lines. Not sure why. Warehouse 10 only has 8 items in it but it seems to be showing all items.

Secondly, this only shows the item cost if the item has a valuation method of standard with a value set on the item master data record. I need it to see the item cost against the item in warehouse 10.

Secondly, rather than Last purchase price I wish to be able to use the price for various price lists. Could I set a condition whereby I need to select a price list before the query is executed?

Thanks in advance.

Steve.