Alternatives to a Work File?

[jsplice]

Hey everyone. I'm trying to think of ways to simulate a workfile without actually creating a physical file on the system. I've used arrays and data structures in the past, but that was for small amounts of data; the file I am processing now will most likely have 10s of thousands of records, if not 100s of thousands. The reason I need this "workfile" is because I have a file that needs to be sorted by company division, and this field is not included in the file that I am processing. So I need to use the facility number in the file and look up the division it is under, and have the whole file sorted by division so that I can create a report. One option that comes to mind is embedded SQL; I'm not entirely familiar with it, but I would like to learn more about it. Do you guys know of any other options? And if embedded SQL is the best option here, is there any good online documentation for it?

Thanks

 

[bwtc]

Try using a data queue

 

[jsplice]

I'm not so sure that a data queue would work. I need to have this file sorted by several more fields, not just the division. Sorry for not specifying that earlier.

 

[flapeyre]

If this is something that will run a lot, how about creating a join logical over the two physical files in question? (You can do the same thing with OPNQRYF, but the program will take longer to run because it will have to build its own access path every time).

Tibi gratias agimus quod nihil fumas.

 

[jsplice]

That actually sounds like a good idea if it will work. I've never made a join logical before. Basically what I have is one file that is my main data file that is going to used on the report. This file only contains the region number and facility number, and not the division number. So what I have to do is go out to another file, and chain (or join) based on the facility number to retrieve the division number. But if I can do this all in one fowl swoop then that would be perfect.

 

[arrow483]

If you do it very often, a join LF is prob the best. If you do it less often (such a a few times each day), you may be btter off with a SQL view.

CREATE VIEW file1j as (select a.region, a.facility as facility, b.division from file1 A
left out join file2 B on a.facility = b.facility)