Mark,
READNEXT works on any multi-attributed array not just on active select
lists.
Look up the FROM option or the SELECT command in the Unibasic manual.
And of course instead of reading from the &SAVEDLISTS& file you might
rather want to execute 'get-list listname'
I gathered that the product code (ItmID) is the only link between your
two tables, but you didn't mention that you actually indexed this field.
AFAIK a select will only use one index (the first one?) and then use the
returned Ids to do the further selection by reading all items and
testing them against your other selection criteria to build the select list.
Looking at your select statement:
CmdToFindAvail = \SELECT TABLE2 WITH ItmID = "\:InvIT:\" AND WITH QtyAva
> 0 and PurDt <= "\:oconv(InvDt,'D4\'):\" BY PurDt BY PurID BY
LinIt\
Indexing ItmID would make sense, since it will leave you with a list of
all Purchase order lines for this product.
QtyAva doesn't look like a good field to index and InvDt isn't a
candidate for indexing either because you are using <=.
But do you really need to sort them as well?
I still can't figure out, what you actually try to achieve in TABLE3
I would probably use a appropriately sized work file with an id of
ItmID and make 2 passes.
1. read all records from TABLE2 and update the work file with a sorted
multi-valued field for PurDt using locate and insert.
Use sub-values for the purchase order numbers, line numbers and
received quantities for each value above.
2. read all records from TABLE1 (or use the saved list) and then use the
data from the work file to do the calculation and build TABLE3
If you build an index for ItmID on both tables, you can even use a
sseelect table by ItmID and then only read and write the data to the
work file when ItmID changes.
This has to be quicker than selecting TABLE2 4 Million times in a loop.
And don't forget to clear or delete the work file when you're finished.
Mecki
Mark Smith wrote:
Quoting "Brutzman, Bill" <bi...@hkmetalcraft.com>:
Thanks Bill & Mecki for your quick response....
First, Mecki thats for your suggestion about not using for/next look
on my cicle. And about BUILD.INDEX, yes... I did run BUILD.INDEX i
just typed wrong the command that I used, on my email..:).
About the files/tables, let me explain you a little bit more on detail:
the first table, it's an Invoices TABLE (compressed); with just the
Invoice ID, Date, LineItem, PartId & QTY.
On TABLE2 it's my Purchases/Invetory TABLE with the PO#, Date,
LineItem, PartID & QTY_AVAIL.
What I want to do is reduce the Balance of my TABLE2 from my TABLE1;
my 'only requirement is that the Date of my TABLE2 has to be greater
or equal than the Date on my TABLE2.
So basically I have two cycles:
-Cicle1:
READ each ITEM on TABLE1
-Retrieve Date, PartID & Qty_Required
---- Cicle2:
Look for Purchases/Inv with DATE<= than InvoiceDate Balance>0
and PartID=PartID of Invoice
---------
What I did was select the TABLE1 by Date and then SAVE-LIST
'tosomething' then on the main cicle reading each of the items...
Thanks....for your help
-------
u2-users mailing list
u2-users@listserver.u2ug.org
To unsubscribe please visit http://listserver.u2ug.org/
