Hi,

What is the limit of records in a "IN file " command.

EG:
when i save a file with employee id in it and use the same further down in a table with in file command in where statment, it is throwing a error
" computational field is too large"


any idea?

thanks

Mentioned many times before in posts -

32000 chars

Use join or match instead of IF fieldname EQ (filename) to overcome restriction.

T

With WHERE, the limit is 16,000 characters, and 32K with IF.

There are some tricks.

What I do is split the selection into more than one hold file.

Than I do a define like

DEFINE FILE XXX
SEL1/I1=DECODE CLIENTCODE(HOLD1 ELSE 1);
SEL2/I1=DECODE CLIENTCODE(HOLD2 ELSE 1);
SEL3/I1=DECODE CLIENTCODE(HOLD3 ELSE 1);
SEL4/I1=DECODE CLIENTCODE(HOLD4 ELSE 1);
END
TABLE FILE XXX

....
WHERE SEL1 EQ 0 OR SEL2 EQ 0 OR ....
END

A join would be better, but sometimes you need some other solutions.

The IN FILE technique is a great replacement to avoid doing heterogeneous joins to a relational target. Select the keys from the 'parent' into a save file. Multiply the length of the field by the number of rows (use DST. to make sure you only have unique values) to determine if you are within the size limit. Make sure that the WHERE is done in the TABLE part, not in a DEFINE, so that the proper SQL is generated.

I successfully used and taught this technique to a new developer who had a fixed file he needed to join to DB2 and Teradata.