I have a text file that basically lists a bunch of ID numbers that I'd like to place in an & variable in a program I'm writing. I know I can read a value into a variable using -READ, but at this time I'm only getting the first value. I'd like to use all of the values in my file. I have one ID number per line. How would I read in more than one value? This is what I have so far. In the documentation they only show one value being read in.

FILEDEF IDLIST DISK myfile.ftm
-RUN
-SET &IDNUM = ' ';
-READ IDLIST &IDNUM.A9.

TABLE FILE PROSPECTS
PRINT ID LAST_NAME FIRST_NAME
IF ID EQ '&IDNUM'
END

Based upon your query, why don't you change

IF ID EQ '&IDNUM'

to

WHERE ID IN FILE IDLIST

But if you need to read multiple times, then use a REPEAT loop and the NOCLOSE option to READ.

Thank you!!! This works very well.

I believe there is a limit to the number of lines I can have in my external file using the IN FILE in my where statement.

Yes, 16 or 32K depending syntax or release.

See Reading Selection Values From a File

Regards,
Mikel

From tech support:

quote:

when using WHERE ... IN FILE syntax, you should try using IF ... EQ (ddname) syntax instead. Currently, the supported number of bytes for IF ... EQ (ddname) syntax is approximately 32K for FOCUS databases and relational files. In the case of WHERE ... IN FILE syntax, the limit is approximately 16,000 bytes.

As well, you may reach a limit when the FOCUS code is translated to SQL - I think there's a limit of 1000 values for a where statement in Oracle.

You may need to use MATCH FILE...

-READ IDLIST NOCLOSE &IDNUM.A9
-IF &IORETURN NE 0 GOTO EOJ;

it'll work like a charm. next loop thru, you'll read the next record.
&IORETURN will be some goofy number like -1 when it hits endoffile.

Pietro and Susannah,

I have been doing -READ in a loop and have not used noclose option...seems to be working fine. So what is the difference?

Vipul

The NOCLOSE option ensures that, if you have FOCUS code (TABLE FILE etc.) within the loop, the progam does not get lost while READing the file. If you had FOCUS code within the loop without NOCLOSE, the program would always read the first row and you would get an endless loop.

Cheers.

Yup,

I read the documentation. I am using db2 on unix.
I am not using the external files.

Vipul

I just wanted to clarify the need for the NOCLOSE. When doing a -READ on a file, the file will be closed when FOCUS executes the commands on the command stack or, in other words, when it encounters a -RUN or the end of a FOCEXEC. Having TABLE FILE in your request after a -READ does not cause FOCUS to close the file unless, of course, you have a -RUN after the TABLE FILE.

One of the old ways of getting around this issue of the file closing was simply to do a loop around the -READ and TABLE FILE code (without a -RUN in the loop) for each value/record read from the file, causing FOCUS to stack all the commands. When the loop had read all the values in the file, it would then drop out of the loop to a -RUN which would then cause FOCUS to run all the stacked TABLE FILE requests. Not pretty, but it worked. The NOCLOSE is a much better way of doing this.

Just wanted to give a little clarification on this issue.