How do i use a workfile

[shorty545]

I am working on extracting data from a progress database for expense reporting. I am able to dump all the data into an excell file and sort from there. However, what I want to do is sort the data by vendor and account before displaying the data to the screen. I was given a hint to save the data into a temporary work file and then extract the data from that work file. I however cannot logically follow how a workfile works. Can anyone help explain how to dump my data into a workfile?

 

[carolynh]

You could create a temp table and sort it that way also, it is pretty much the same concept. It wouold be

DEFINE TEMP-TABLE temp-table-name
[ LIKE table-name
[ VALIDATE ]
[ USE-INDEX index-name [ AS PRIMARY ] ] ...
]
[ FIELD field-name
{ AS data-type | LIKE field [ VALIDATE ] }
[ field-options ]
]

The when you do a FOR EACH on your db do a CREATE TEMP-TABLE and assign the values from your db table to your new temp-table and do your sorting.

Hope this help!

 

[richtpt]

You will want to use a temp-table instead of a workfile. Workfiles are in memory only and if you fill up the memory set aside for the workfile, your program will crash. Temp-tables will use memory first, but then go to disk if needed.

Rich

 

[HelloMike]

And you'll want to put an index on your temp-table by vendor and account.

 

[richtpt]

Depends on how many records you're putting into the temp-table. You don't always need an index. I've tried putting about 2,000 records in without an index and with an index, and I didn't see enough of a difference to make using an index a must. If I were doing 10,000 or 100,000 records or more, and a lot of fields, then yes, I would use an index. Adding the index is extra overhead of not only creating the temp-table but creating the index.

As with so many things, it depends on what you are doing.

Rich

 

[HelloMike]

Hmm. I must admit I haven't done any benchmarking on indexing a temp-table vs sorting an unindexed temp-table, but I tend to put indexes in anyway so I can be reasonably sure that there won't be any performance problems a year or 2 down the line.

Mike.

 

[richtpt]

That's a good point. It all depends on what you're doing and how much data you think might end up in the table.

Rich