I'm hoping someone can help me out with this. I'm trying to write a query that will return the last transaction for each item in our warehouse. To do this, I'm using 2 files, first our location detail which has a list of all the locations in our warehouse. This file only has about 60,000 records, so I don't think indexing is a big impact. However, I'm joining this file to a 12,000,000 record transaction history file, and I'm having a horrible time getting the right logical built.
basically, I'm doing the following :
select min(loc) FirstLocaion, item, version, lasttran from locations left join (select item2, version2, max(trandate) lasttrans from transactions) on item=item2 and version=version2 group by item, version, lasttran
I have a logical built on the transactions that keys item, version, and trandate, and then does several select and omit functions to keep only the transactions that indicate movement.
Everything I try turns into a 10 min run to retrieve the information I'm looking for. I'm thinking my best bet might be to make the subselect a logical, but I can't find any DDS documentation on how to do that type of a select.