Quoting "Brutzman, Bill" <[email protected]>:
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
------------------------------------------------------
Hi Mark,
Doing selects in a loop is never a good idea especially on large files
and definitely not
a sorted one like you are trying to do.
But if you really have no other option, then you should at least
create an index on ItmID
on TABLE2.
I don't know about Universe, but don't you have to do BUILD.INDEX
after the creation of a
new index?
I've only ever read index data in a Basic program on D3, so I can't
tell how it is done
in Universe, but that would of course be a preferable option to
executing a select
statement if you haven't got a cross-reference anywhere.
And I would strongly recommend to use READNEXT in a loop instead FOR...NEXT.
Mecki
While I am not entirely clear on the top-down business-logic
requirements-analysis thing...I presume that we want to subtract Table2
from Table1. Perhaps the following is helpful. The index and the
record contents are created at the same time as Table3 is generated.
Open Table1 to F.Table1 else null
Open Table2 to F.Table2 else null
Open Table3 to F.Table3 else null
Blow away Table3...
Clearselect ALL
Select F.Table1
done = false
loop
readnext Inventory.ID else done = true
until done do
read R.Inv.1 from F.Table1, Inventory.ID else R.Inv.1 = ''
This.Qty.1 = R.Inv1<5>
read R.Inv.2 from F.Table2, Inventory.ID else R.Inv.2 = ''
This.Qty.2 = R.Inv.2<5>
Delta.Qty = This.Qty.1 - This.Qty.2
crt @(10,10) : This.Count
crt @(10,10) : Inventory.ID
crt @(10,10) : Delta.Qty
R.Inv.3 = R.Inv.1
R.Inv.3<5> = Delta.Qty
write R.Inv.3 on F.Table3, Inventory.ID
repeat
--Bill
________________________________________________________________________
________________
-----Original Message-----
From: [email protected]
[mailto:[email protected]] On Behalf Of Mark Smith
Sent: Wednesday, April 01, 2009 10:04 PM
To: [email protected]
Subject: [U2] Question about indexing
Hello everyone,
I have a question about indexing. I have just a little of experience, so
be patient with me.:)
First of all I'm using Universe 10.0.4.0 and SB 5.2 on a Windows 2003
Server on a Dual-Core Opteron Server and 4Gb of RAM & SCSI drives
I wanted to simplify (just a little) my database, to improve the
performance. with the changes I made, i got to the point that I have to
compare 2 tables:
TABLE1
DetID - KeyField
InvID -
InvDt -
LinIt -
ItmType
ItmID -
QtyReq -
TABLE2
DetID - KeyField
PurID -
PurDt -
LinIt -
ItmID -
ItmType -
QtyPur -
QtyAva -
What I want to do is to scan the first table/file (TABLE1), substract
inventory ammounts and create a third file (LOG)with the following:
TABLE3
DetID
InvID
InvLI
PurID
PurLI
QtyDis
On TABLE1 I have around 4,000,000 records; and on TABLE2 i have around
60,000.
I have been testing on how to create index. CREATE.INDEX / UPDATE.INDEX.
But after looking on the manuals and online (everything points into here
:)) i don't know how that works....
How I can have access to the index?
Is the index applied when i use a SELECT command?
I created the index like this:
CREATE.INDEX TABLE1 InvID InvDt
CREATE.INDEX TABLE2 PurID ItmType PurDt QtyAva
and I updated the indexes like this:
UPDATE.INDEX TABLE1 ALL
UPDATE.INDEX TABLE2 ALL
mmm ,mmm now that I'm writing this...i'm thinking... do I need to create
an index for the third file? (probably an obvious question).
By the way I'm running this from a basic program, something like this:
(I didn't copy/paste the code, but it's something similar....)
.........
MyListFromTable1 = {I get this variable from after reading from my
&SAVEDLISTS& file}
QtyOfItemsOnList1 = dcount(MyListFromTable1,CHAR(254))
for i=1 to QtyOfItemsOnList1
c_ItemID = MyListFromTable1<i>
read RegItmTable1 from TABLE1_file,c_ItemID else
ErrMsg = "Unable to read [":c_ItemID:"]"
goto ExtitSub
end
InvID = RegItmTable1<1>
InvDt = RegItmTable1<2>
InvLI = RegItmTable1<3>
InvIT = RegItmTable1<4>
InvQT = RegItmTable1<6>
CmdToFindAvail = \SELECT TABLE2 WITH ItmID = "\:InvIT:\" AND WITH
QtyAva > 0 and PurDt <= "\:oconv(InvDt,'D4\'):\" BY PurDt BY PurID BY
LinIt\
EXECUTE CmdToFindAvail
....
....
......
next i
Thanks!
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
-------
u2-users mailing list
[email protected]
To unsubscribe please visit http://listserver.u2ug.org/
