I have a report where the user can select over 1000 values to be used in a WHERE statement. SQL only allows 1000, so I thought I could write WebFOCUS code that looks like this - this should allow for more than 1000 values:

TABLE FILE TABLE1
SUM MY_MEASURE
BY MY_DIMENSION
WHERE
NODE_NO EQ 1
OR NODE_NO EQ 3
OR NODE_NO EQ 7
OR NODE_NO EQ 1000014
OR NODE_NO EQ 1000015
OR NODE_NO EQ 1000639
OR NODE_NO EQ 1000793
OR NODE_NO EQ 8
...
END

But the SQL translator generates this:

SELECT
T1.MY_DIMENSION,
SUM(T1.MY_MEASURE)
FROM
TABLE1 T1
WHERE
(T1.NODE_NO IN(1, 3, 7, 1000014, 1000015, 1000639, 1000793, 8...)

It puts all the values into one IN statement, which will trigger an error.

How do I code my WebFOCUS request so that this does not happen?

Part 2:

Strangely, I do not get a SQL error when using over 1000 values (in Oracle the error is "ORA-01795: maximum number of expressions in a list is 1000"). Instead, I get no error and no data .

This message has been edited. Last edited by: Francis Mariani,

Francis,

Did you try:

  
NODE_NO EQ 1
OR 3
OR 7
OR 1000014
OR 1000015
OR 1000639
OR 1000793
OR 8

or

  
(NODE_NO EQ 1)
OR (NODE_NO EQ 3)
OR (NODE_NO EQ 7)
OR (NODE_NO EQ 1000014)
OR (NODE_NO EQ 1000015)
OR (NODE_NO EQ 1000639)
OR (NODE_NO EQ 1000793)
OR (NODE_NO EQ 8)

I have a monthly request where the user sends above 20,000 serials for which I need to generate a report with all needed columns. For this I faced the same 1000 limit issue. So, I have created a table in the database and load those serials into the table and use this newly created table as a inner join to the other tables which solved my issue.

Danny, both those versions generate the IN statement.

Rao, I cannot create a table on the fly - this is an online report, run by many people.

Divide and conquer, using MORE and -REPEAT

Jack, genius!

I was using MORE for a different business reason so I didn't think of it - your solution is great and works.

Here is some of my code, for illustrative purposes I used a limit of 500, but will probably change it to 1000.

-* Determine the number of selected Nodes and create individual Node parameters --------------------
-SET &NODE_COUNT = 1;

-LOOP0

-SET &NODE.&NODE_COUNT = GETTOK(&P_NODE_NO, &P_NODE_NO.LENGTH, &NODE_COUNT, ',', 12, 'A12');

-IF &NODE.&NODE_COUNT EQ '' GOTO ENDLOOP0;

-SET &NODE_COUNT = &NODE_COUNT + 1;

-GOTO LOOP0

-ENDLOOP0

-SET &NODE_COUNT = &NODE_COUNT - 1;
-TYPE NODE_COUNT &NODE_COUNT

-* Determine the number of iterations for the MORE statement to handle a Node filter of over 1000 values ---
-* The first 500 are processed in the main request, the rest are processed in batches of 500

-SET &REPEAT_LIMIT = 500;
-SET &REPEAT_MORE = INT( (&NODE_COUNT - 1 ) / &REPEAT_LIMIT );

-TYPE REPEAT_MORE &REPEAT_MORE

...

TABLE FILE MY_TABLE
SUM
MY_MEASURE
BY MY_DIMENSION

WHERE

-SET &NODE_LIMIT = IF &NODE_COUNT GT &REPEAT_LIMIT THEN &REPEAT_LIMIT ELSE &NODE_COUNT;

-REPEAT ENDLOOP1_IS FOR &I FROM 1 TO &NODE_LIMIT

-SET &OR = IF &I EQ 1 THEN '' ELSE 'OR';

&OR NODE_NO EQ &NODE.&I

-ENDLOOP1_IS

ON TABLE HOLD AS H001 MISSING OFF

-IF &NODE_COUNT LE &REPEAT_LIMIT GOTO END_REPEAT_MORE;

-SET &NODE_START = &REPEAT_LIMIT + 1;

-REPEAT END_REPEAT_MORE &REPEAT_MORE TIMES

MORE
FILE MY_TABLE

WHERE

-SET &NODE_LIMIT = IF &NODE_COUNT GT (&NODE_START + &REPEAT_LIMIT) THEN &NODE_START + &REPEAT_LIMIT - 1 ELSE &NODE_COUNT;

-REPEAT ENDLOOP2_IS FOR &I FROM &NODE_START TO &NODE_LIMIT

-SET &OR = IF &I EQ &NODE_START THEN '' ELSE 'OR';

&OR NODE_NO EQ &NODE.&I

-ENDLOOP2_IS

-SET &NODE_START = &NODE_START + &REPEAT_LIMIT;

-END_REPEAT_MORE
END
-RUN

Thanks very much.

Francis, how many nodes (records) are in the database? Can you put other selection criteria in the report to cut down on the number of retrieved records? Really, you just need a decode to select the records you need as a define:

ok/i1 = decode field();

...

table file ...
...
if ok eq 1
if
end

IBI Development

The user can select over 1500 nodes from a Tree Control.