I recently upgraded from 7.1x to 7.67 and so far the only problem is a report which used hundreds of values in an IN statement for a defined field. It seems that 767 stops at 387 values, the 388th makes it abend. It has nothing do do with the value lengths, only the quantity of them.

It use to work in 7.1 and now in 7.6 it doesn't. I haven't seen any documentation on it or have heard back from my IBI case. Just wondering if anyone read about this, if not here's a heads up.

Here is the test script I submitted to IBI. If you remove the 388, it works.

DEFINE FILE CAR
FIELD1/I8 = IF SALES IN (
1,2,3,4,5,6,7,8,9,10,
11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50,
51,52,53,54,55,56,57,58,59,60,
61,62,63,64,65,66,67,68,69,70,
71,72,73,74,75,76,77,78,79,80,
81,82,83,84,85,86,87,88,89,90,
91,92,93,94,95,96,97,98,99,100,
101,102,103,104,105,106,107,108,109,110,
111,112,113,114,115,116,117,118,119,120,
121,122,123,124,125,126,127,128,129,130,
131,132,133,134,135,136,137,138,139,140,
141,142,143,144,145,146,147,148,149,150,
151,152,153,154,155,156,157,158,159,160,
161,162,163,164,165,166,167,168,169,170,
171,172,173,174,175,176,177,178,179,180,
181,182,183,184,185,186,187,188,189,190,
191,192,193,194,195,196,197,198,199,200,
201,202,203,204,205,206,207,208,209,210,
211,212,213,214,215,216,217,218,219,220,
221,222,223,224,225,226,227,228,229,230,
231,232,233,234,235,236,237,238,239,240,
241,242,243,244,245,246,247,248,249,250,
251,252,253,254,255,256,257,258,259,260,
261,262,263,264,265,266,267,268,269,270,
271,272,273,274,275,276,277,278,279,280,
281,282,283,284,285,286,287,288,289,290,
291,292,293,294,295,296,297,298,299,300,
301,302,303,304,305,306,307,308,309,310,
311,312,313,314,315,316,317,318,319,320,
321,322,323,324,325,326,327,328,329,330,
331,332,333,334,335,336,337,338,339,340,
341,342,343,344,345,346,347,348,349,350,
351,352,353,354,355,356,357,358,359,360,
361,362,363,364,365,366,367,368,369,370,
371,372,373,374,375,376,377,378,379,380,
381,382,383,384,385,386,387,388) THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT FIELD1
END

Same issue with 7.6.6 on unix

Tried this in 764 (windows) - same problem.
The obvious workaround for this would be to put the list in an external file and use that in stead of the list in the define.
Mayne you already thought of that workaround, but it is not mentioned in your entry.
The request would then look something like:

APP FI FPTEST DISK SESSION/FPTEST.TXT

DEFINE FILE CAR
FIELD1/I8 = IF SALES IN FILE FPTEST THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT SALES FIELD1 
END

I tested this with a list of over 500 entries (in 767), and it gave me the correct result.

Hope this helps.

The solution GamP gives has some limitions too.
But then you can create two or more external files and combine that coding, something like.

APP FI FPTEST1 DISK SESSION/FPTEST1.TXT
APP FI FPTEST2 DISK SESSION/FPTEST2.TXT
APP FI FPTEST3 DISK SESSION/FPTEST3.TXT

DEFINE FILE CAR
FIELD1/I8 = IF SALES IN FILE FPTEST1 THEN 1 ELSE 0;
FIELD2/I8 = IF SALES IN FILE FPTEST2 THEN 1 ELSE 0;
FIELD3/I8 = IF SALES IN FILE FPTEST3 THEN 1 ELSE 0;
FIELD4/I8 = FIELD1+FIELD2+FIELD3;

END
TABLE FILE CAR
PRINT SALES FIELD4 
END

slightly OT but a similar limitation exists in DECODE where you can only DECODE up to about 50 values at a time. Not sure if this has always been present and ultimately its easy to get around i can the exact number if interested...

As it used to work it should still work.

Easy fix is

DEFINE FILE CAR
FIELD1/I8 = IF SALES IN (
1,2,3,4,5,6,7,8,9,10,...387) THEN 1 ELSE 0;
FIELD1 = IF SALES IN (
388,389,390,391...774) THEN 1 ELSE FIELD1;
etc.
END
TABLE FILE CAR
PRINT FIELD1
END

Frank - I like your solution best however, I can't get it to work.. so maybe I don't like it that much. One file works, but then on the next define field it errors out with a "numeric in place of alpha...". I checked my text files and the data is OK.
Is the "IN FILE" limited to just one external file?
Does this work for you?

Also, it seems if I choose to do the "IN" statement using multiple define fields, the SQL translator creates a ugly mess which exceeds the acceptable size of an SQL statement.

Thanks

Shawn

It should work as suggested IMHO.
Can you post your complete code?

Did you get the error if you only use the second file?

Frank - I used your code (mostly). I even pointed both allocations to the same file. If I comment out the second define, it works. Other wise I get the error. Also something else worth noting, when it does work, it only works into an integer field. An A1 with a 'Y' or 'N' always produces the "else".
My code below:

APP FI FPTEST1 DISK END_USER_INPUT/WELDING.TXT
APP FI FPTEST2 DISK END_USER_INPUT/WELDING.TXT
DEFINE FILE CAR
FIELD1/I8 = IF SALES IN FILE FPTEST1 THEN 1 ELSE 0;
FIELD2/I8 = IF SALES IN FILE FPTEST2 THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT SALES FIELD1 FIELD2
END

Shawn

If you mail me the "welding" files I will test it myself (tomorrow, at the office).

BTW update your signature with system information and version numbers.

frank.terlien@gmail.com

Frank - There is only one value in the file; 100.

With two or more of these select statements in the define, I too can not get it to work, not even if I point to different files with different contents.
As long as I only use one of these IN FILE things, everything is ok.
Just for the fun of it, I tested the one file approach with over 3900 entries in the test file - no problem. It gave me the correct result.
And as Frank stated, yes there is a limitation: the total amount of test values can not exceed 32 KB.
So it all depends on the size of the filed to be tested and the amount of test values if you hit this limit or not.

Hope this helps...

I did not test my first post, I just thought this should work because I use this more or less with a decode function.

That does work!

Shawn, you've most likely fixed the problem by now, but here's another solution: break the values into chunks, though I don't know why your original code worked in v5.3 and doesn't in v7.6.

DEFINE FILE CAR
FIELD1/I8 =
IF SALES IN (
1,2,3,4,5,6,7,8,9,10,
11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50,
51,52,53,54,55,56,57,58,59,60,
61,62,63,64,65,66,67,68,69,70,
71,72,73,74,75,76,77,78,79,80,
81,82,83,84,85,86,87,88,89,90,
91,92,93,94,95,96,97,98,99,100)
THEN 1 ELSE
IF SALES IN (
101,102,103,104,105,106,107,108,109,110,
111,112,113,114,115,116,117,118,119,120,
121,122,123,124,125,126,127,128,129,130,
131,132,133,134,135,136,137,138,139,140,
141,142,143,144,145,146,147,148,149,150,
151,152,153,154,155,156,157,158,159,160,
161,162,163,164,165,166,167,168,169,170,
171,172,173,174,175,176,177,178,179,180,
181,182,183,184,185,186,187,188,189,190,
191,192,193,194,195,196,197,198,199,200)
THEN 1 ELSE
IF SALES IN (
201,202,203,204,205,206,207,208,209,210,
211,212,213,214,215,216,217,218,219,220,
221,222,223,224,225,226,227,228,229,230,
231,232,233,234,235,236,237,238,239,240,
241,242,243,244,245,246,247,248,249,250,
251,252,253,254,255,256,257,258,259,260,
261,262,263,264,265,266,267,268,269,270,
271,272,273,274,275,276,277,278,279,280,
281,282,283,284,285,286,287,288,289,290,
291,292,293,294,295,296,297,298,299,300)
THEN 1 ELSE
IF SALES IN (
301,302,303,304,305,306,307,308,309,310,
311,312,313,314,315,316,317,318,319,320,
321,322,323,324,325,326,327,328,329,330,
331,332,333,334,335,336,337,338,339,340,
341,342,343,344,345,346,347,348,349,350,
351,352,353,354,355,356,357,358,359,360,
361,362,363,364,365,366,367,368,369,370,
371,372,373,374,375,376,377,378,379,380,
381,382,383,384,385,386,387,388)
THEN 1 ELSE 0;
END
TABLE FILE CAR
PRINT FIELD1
END

Francis - Chaining works to give me an new limit of 439 values versus the 387 limit for un-chained. You can, like some previous posts have suggested, chain your field's result to a new define field and continue the criteria. Even that has its limits, but that limit (for me) is caught in the SQL translator. In other words, using the CAR file I can chain multiple fields comparing beyond 900 values. When I try that against a db2 data source, my agent crashes. Whats worse is it isn't even passing the IN logic to db2.
Anyway, my user has nearly 1000 values which doesn't work in any secenario I have tried to create. IBI has confirmed that there is a change in the way this performed in 71 versus 76, but I haven't heard beyond that.

Thanks,
Shawn

Shawn,

I have also a DB2 instance active, and my WebFOCUS release is 764.
The approach I gave earlier works fine in this setup. The code I used to test this is:

APP FI FPTEST DISK SESSION/FPTEST.FTM
-RUN

-SET &TELLER = 9000;
-SET &KOMMA = ',';
-REPEAT :LOOP 2000 TIMES
-WRITE FPTEST &TELLER
-SET &TELLER=&TELLER+1;
-:LOOP
-WRITE FPTEST &TELLER
-RUN

SET TRACEON = STMTRACE//CLIENT
SET TRACEUSER = ON

DEFINE FILE LWTK600
YES/I6 = IF GRHD_CD IN FILE FPTEST THEN 1 ELSE 0;
END
TABLE FILE LWTK600
PRINT GRHD_CD YES
END

This code generates a test file of 2001 values and then it runs against a db2 table (windows).
The trace shows that it does not send the IN clause to db2, but WebFOCUS will handle the IN selection. It gives me the correct results without any error.

Hope this helps...