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/
