I have a tree control on an html page that can pass too many values to a report WHERE clause against an ORACLE view such that it throws an error:
ORA-01795: maximum number of expressions in a list is 1000

So, I decided to HOLD in FOCUS file the values passed by writing a mas file and FILEDEF a data file as input.
This step works until I reach 256 values. I get no error and no output (I execute a TABLE FILE focusfile after allocating the input to the tree values to see what would be held if I added the ON TABLE HOLD).

The following code fails. If I remove 1 value from the list from the list of &TIMESCALE_DTX then it works.

Ideas?
Thanks!

-SET &TIMESCALE_DTX=
-'''2014/11/17'' OR ''2014/11/18'' OR ''2014/11/19'' OR ''2014/11/20'' OR ''2014/11/21'' OR ''2014/11/22'' OR ''2014/11/23'' OR ''2014/11/24'' OR ''2014/11/25'' OR ''2014/11/26'' OR ''2014/11/27'' OR ''2014/11/28'' OR ''2014/11/29'' OR ''2014/11/30'' OR ''2014/12/01'' OR ''2014/12/02'' OR ''2014/12/03'' OR ''2014/12/04'' OR ''2014/12/05'' OR ''2014/12/06'' OR ''2014/12/07'' OR ''2014/12/08'' OR ''2014/12/09'' OR ''2014/12/10'' OR ''2014/12/11'' OR ''2014/12/12'' OR ''2014/12/13'' OR ''2014/12/14'' OR ''2014/12/15'' OR ''2014/12/16'' OR ''2014/12/17'' OR ''2014/12/18'' OR ''2014/12/19'' OR ''2014/12/20'' OR ''2014/12/21'' OR ''2014/12/22'' OR ''2014/12/23'' OR ''2014/12/24'' OR ''2014/12/25'' OR ''2014/12/26'' OR ''2014/12/27'' OR ''2014/12/28'' OR ''2014/12/29'' OR ''2014/12/30'' OR ''2014/12/31'' OR ''2015/01/01'' OR ''2015/01/02'' OR ''2015/01/03'' OR ''2015/01/04'' OR ''2015/01/05'' OR ''2015/01/06'' OR ''2015/01/07'' OR ''2015/01/08'' OR ''2015/01/09'' OR ''2015/01/10'' OR ''2015/01/11'' OR ''2015/01/12'' OR ''2015/01/13'' OR ''2015/01/14'' OR ''2015/01/15'' OR ''2015/01/16'' OR ''2015/01/17'' OR ''2015/01/18'' OR ''2015/01/19'' OR ''2015/01/20'' OR ''2015/01/21'' OR ''2015/01/22'' OR ''2015/01/23'' OR ''2015/01/24'' OR ''2015/01/25'' OR ''2015/01/26'' OR ''2015/01/27'' OR ''2015/01/28'' OR ''2015/01/29'' OR ''2015/01/30'' OR ''2015/01/31'' OR ''2015/02/01'' OR ''2015/02/02'' OR ''2015/02/03'' OR ''2015/02/04'' OR ''2015/02/05'' OR ''2015/02/06'' OR ''2015/02/07'' OR ''2015/02/08'' OR ''2015/02/09'' OR ''2015/02/10'' OR ''2015/02/11'' OR ''2015/02/12'' OR ''2015/02/13'' OR ''2015/02/14'' OR ''2015/02/15'' OR ''2015/02/16'' OR ''2015/02/17'' OR ''2015/02/18'' OR ''2015/02/19'' OR ''2015/02/20'' OR ''2015/02/21'' OR ''2015/02/22'' OR ''2015/02/23'' OR ''2015/02/24'' OR ''2015/02/25'' OR ''2015/02/26'' OR ''2015/02/27'' OR ''2015/02/28'' OR ''2015/03/01'' OR ''2015/03/02'' OR ''2015/03/03'' OR ''2015/03/04'' OR ''2015/03/05'' OR ''2015/03/06'' OR ''2015/03/07'' OR ''2015/03/08'' OR ''2015/03/09'' OR ''2015/03/10'' OR ''2015/03/11'' OR ''2015/03/12'' OR ''2015/03/13'' OR ''2015/03/14'' OR ''2015/03/15'' OR ''2015/03/16'' OR ''2015/03/17'' OR ''2015/03/18'' OR ''2015/03/19'' OR ''2015/03/20'' OR ''2015/03/21'' OR ''2015/03/22'' OR ''2015/03/23'' OR ''2015/03/24'' OR ''2015/03/25'' OR ''2015/03/26'' OR ''2015/03/27'' OR ''2015/03/28'' OR ''2015/03/29'' OR ''2015/03/30'' OR ''2015/03/31'' OR ''2015/04/01'' OR ''2015/04/02'' OR ''2015/04/03'' OR ''2015/04/04'' OR ''2015/04/05'' OR ''2015/04/06'' OR ''2015/04/07'' OR ''2015/04/08'' OR ''2015/04/09'' OR ''2015/04/10'' OR ''2015/04/11'' OR ''2015/04/12'' OR ''2015/04/13'' OR ''2015/04/14'' OR ''2015/04/15'' OR ''2015/04/16'' OR ''2015/04/17'' OR ''2015/04/18'' OR ''2015/04/19'' OR ''2015/04/20'' OR ''2015/04/21'' OR ''2015/04/22'' OR ''2015/04/23'' OR ''2015/04/24'' OR ''2015/04/25'' OR ''2015/04/26'' OR ''2015/04/27'' OR ''2015/04/28'' OR ''2015/04/29'' OR ''2015/04/30'' OR ''2015/05/01'' OR ''2015/05/02'' OR ''2015/05/03'' OR ''2015/05/04'' OR ''2015/05/05'' OR ''2015/05/06'' OR ''2015/05/07'' OR ''2015/05/08'' OR ''2015/05/09'' OR ''2015/05/10'' OR ''2015/05/11'' OR ''2015/05/12'' OR ''2015/05/13'' OR ''2015/05/14'' OR ''2015/05/15'' OR ''2015/05/16'' OR ''2015/05/17'' OR ''2015/05/18'' OR ''2015/05/19'' OR ''2015/05/20'' OR ''2015/05/21'' OR ''2015/05/22'' OR ''2015/05/23'' OR ''2015/05/24'' OR ''2015/05/25'' OR ''2015/05/26'' OR ''2015/05/27'' OR ''2015/05/28'' OR ''2015/05/29'' OR ''2015/05/30'' OR ''2015/05/31'' OR ''2015/06/01'' OR ''2015/06/02'' OR ''2015/06/03'' OR ''2015/06/04'' OR ''2015/06/05'' OR ''2015/06/06'' OR ''2015/06/07'' OR ''2015/06/08'' OR ''2015/06/09'' OR ''2015/06/10'' OR ''2015/06/11'' OR ''2015/06/12'' OR ''2015/06/13'' OR ''2015/06/14'' OR ''2015/06/15'' OR ''2015/06/16'' OR ''2015/06/17'' OR ''2015/06/18'' OR ''2015/06/19'' OR ''2015/06/20'' OR ''2015/06/21'' OR ''2015/06/22'' OR ''2015/06/23'' OR ''2015/06/24'' OR ''2015/06/25'' OR ''2015/06/26'' OR ''2015/06/27'' OR ''2015/06/28'' OR ''2015/06/29'' OR ''2015/06/30'' OR ''2015/07/01''';
-SET &TIMESCALE_DT=&TIMESCALE_DTX.QUOTEDSTRING |' OR ''2015/07/02'' OR ''2015/07/03'' OR ''2015/07/04'' OR ''2015/07/05'' OR ''2015/07/06'' OR ''2015/07/07'' OR ''2015/07/08'' OR ''2015/07/09'' OR ''2015/07/10'' OR ''2015/07/11'' OR ''2015/07/12'' OR ''2015/07/13'' OR ''2015/07/14'' OR ''2015/07/15'' OR ''2015/07/16'' OR ''2015/07/17'' OR ''2015/07/18'' OR ''2015/07/19'' OR ''2015/07/20'' OR ''2015/07/21'' OR ''2015/07/22'' OR ''2015/07/23'' OR ''2015/07/24'' OR ''2015/07/25'' OR ''2015/07/26'' OR ''2015/07/27'' OR ''2015/07/28'' OR ''2015/07/29'' OR ''2015/07/30''';

-*-'''2014/12/28'' OR ''2014/12/29'' OR ''2014/12/30'' OR ''2014/12/31'' OR ''2015/01/01'' OR ''2015/01/02'' OR ''2015/01/03'' OR ''2015/01/04'' OR ''2015/01/05'' OR ''2015/01/06'' OR ''2015/01/07'' OR ''2015/01/08'' OR ''2015/01/09'' OR ''2015/01/10'' OR ''2015/01/11'' OR ''2015/01/12'' OR ''2015/01/13'' OR ''2015/01/14'' OR ''2015/01/15'' OR ''2015/01/16'' OR ''2015/01/17'' OR ''2015/01/18'' OR ''2015/01/19'' OR ''2015/01/20'' OR ''2015/01/21'' OR ''2015/01/22'' OR ''2015/01/23'' OR ''2015/01/24'' OR ''2015/01/25'' OR ''2015/01/26'' OR ''2015/01/27'' OR ''2015/01/28'' OR ''2015/01/29'' OR ''2015/01/30'' OR ''2015/01/31'' OR ''2015/02/01'' OR ''2015/02/02'' OR ''2015/02/03'' OR ''2015/02/04'' OR ''2015/02/05'' OR ''2015/02/06'' OR ''2015/02/07'' OR ''2015/02/08'' OR ''2015/02/09'' OR ''2015/02/10'' OR ''2015/02/11'' OR ''2015/02/12'' OR ''2015/02/13'' OR ''2015/02/14'' OR ''2015/02/15'' OR ''2015/02/16'' OR ''2015/02/17'' OR ''2015/02/18'' OR ''2015/02/19'' OR ''2015/02/20'' OR ''2015/02/21'' OR ''2015/02/22'' OR ''2015/02/23'' OR ''2015/02/24'' OR ''2015/02/25'' OR ''2015/02/26'' OR ''2015/02/27'' OR ''2015/02/28'' OR ''2015/03/01'' OR ''2015/03/02'' OR ''2015/03/03'' OR ''2015/03/04'' OR ''2015/03/05'' OR ''2015/03/06'' OR ''2015/03/07'' OR ''2015/03/08'' OR ''2015/03/09'' OR ''2015/03/10'' OR ''2015/03/11'' OR ''2015/03/12'' OR ''2015/03/13'' OR ''2015/03/14'' OR ''2015/03/15'' OR ''2015/03/16'' OR ''2015/03/17'' OR ''2015/03/18'' OR ''2015/03/19'' OR ''2015/03/20'' OR ''2015/03/21'' OR ''2015/03/22'' OR ''2015/03/23'' OR ''2015/03/24'' OR ''2015/03/25'' OR ''2015/03/26'' OR ''2015/03/27'' OR ''2015/03/28'' OR ''2015/03/29'' OR ''2015/03/30'' OR ''2015/03/31'' OR ''2015/04/01'' OR ''2015/04/02'' OR ''2015/04/03'' OR ''2015/04/04'' OR ''2015/04/05'' OR ''2015/04/06'' OR ''2015/04/07'' OR ''2015/04/08'' OR ''2015/04/09'' OR ''2015/04/10'' OR ''2015/04/11'' OR ''2015/04/12'' OR ''2015/04/13'' OR ''2015/04/14'' OR ''2015/04/15'' OR ''2015/04/16'' OR ''2015/04/17'' OR ''2015/04/18'' OR ''2015/04/19'' OR ''2015/04/20'' OR ''2015/04/21'' OR ''2015/04/22'' OR ''2015/04/23'' OR ''2015/04/24'' OR ''2015/04/25'' OR ''2015/04/26'' OR ''2015/04/27'' OR ''2015/04/28'' OR ''2015/04/29'' OR ''2015/04/30'' OR ''2015/05/01'' OR ''2015/05/02'' OR ''2015/05/03'' OR ''2015/05/04'' OR ''2015/05/05'' OR ''2015/05/06'' OR ''2015/05/07'' OR ''2015/05/08'' OR ''2015/05/09'' OR ''2015/05/10'' OR ''2015/05/11'' OR ''2015/05/12'' OR ''2015/05/13'' OR ''2015/05/14'' OR ''2015/05/15'' OR ''2015/05/16'' OR ''2015/05/17'' OR ''2015/05/18'' OR ''2015/05/19'' OR ''2015/05/20'' OR ''2015/05/21'' OR ''2015/05/22'' OR ''2015/05/23'' OR ''2015/05/24'' OR ''2015/05/25'' OR ''2015/05/26'' OR ''2015/05/27'' OR ''2015/05/28'' OR ''2015/05/29'' OR ''2015/05/30'' OR ''2015/05/31'' OR ''2015/06/01'' OR ''2015/06/02'' OR ''2015/06/03'' OR ''2015/06/04'' OR ''2015/06/05'' OR ''2015/06/06'' OR ''2015/06/07'' OR ''2015/06/08'' OR ''2015/06/09'' OR ''2015/06/10'' OR ''2015/06/11'' OR ''2015/06/12'' OR ''2015/06/13'' OR ''2015/06/14'' OR ''2015/06/15'' OR ''2015/06/16'' OR ''2015/06/17'' OR ''2015/06/18'' OR ''2015/06/19'' OR ''2015/06/20'' OR ''2015/06/21'' OR ''2015/06/22'' OR ''2015/06/23'' OR ''2015/06/24'' OR ''2015/06/25'' OR ''2015/06/26'' OR ''2015/06/27'' OR ''2015/06/28'' OR ''2015/06/29'' OR ''2015/06/30'' OR ''2015/07/01'' OR ''2015/07/02'' OR ''2015/07/03'' OR ''2015/07/04'' OR ''2015/07/05'' OR ''2015/07/06'' OR ''2015/07/07'' OR ''2015/07/08'' OR ''2015/07/09'' OR ''2015/07/10'' OR ''2015/07/11'' OR ''2015/07/12'' OR ''2015/07/13'' OR ''2015/07/14'' OR ''2015/07/15'' OR ''2015/07/16'' OR ''2015/07/17'' OR ''2015/07/18'' OR ''2015/07/19'' OR ''2015/07/20'' OR ''2015/07/21'' OR ''2015/07/22'' OR ''2015/07/23'' OR ''2015/07/24'' OR ''2015/07/25'' OR ''2015/07/26'' OR ''2015/07/27'' OR ''2015/07/28'' OR ''2015/07/29'' OR ''2015/07/30'' OR ''2015/07/31''';

-SET &TMLEN = &TIMESCALE_DT.LENGTH;
-SET &ATMLEN= 'A'| &TMLEN;
-SET &TIMESCALE_DT = STRREP(&TMLEN, &TIMESCALE_DT, 4, ' OR ' , 1, ',', &TMLEN, &ATMLEN.QUOTEDSTRING);
-SET &TIMESCALE_DT = TRUNCATE(&TIMESCALE_DT);
-TYPE TIMESCALE_DT=&TIMESCALE_DT
-SET &dtNo = 1;
-SET &dt   = GETTOK(&TIMESCALE_DT, &TIMESCALE_DT.LENGTH, &dtNo, ',', 12, 'A12');

FILEDEF DTDATA DISK dtdata.ftm
-RUN

-REPEAT DT_TRIM WHILE &dt GT '';
-SET &dtLEN = &dt.LENGTH;
-SET &AdtLEN= 'A'| &dtLEN;
-SET &dt = STRREP(&dtLEN, &dt, 4, '/' , 0, 'X', &dtLEN, &AdtLEN.QUOTEDSTRING);
-SET &dt = STRREP(&dtLEN, &dt, 4, '''', 0, 'X', &dtLEN, &AdtLEN.QUOTEDSTRING);
-SET &dt = TRUNCATE(&dt);
-WRITE DTDATA &dt
-SET &dtNo = &dtNo + 1;
-SET &dt   = GETTOK(&TIMESCALE_DT, &TIMESCALE_DT.LENGTH, &dtNo, ',', 12, 'A12');

-DT_TRIM
-RUN

FILEDEF DTMAS DISK dtdata.mas
-RUN

-WRITE DTMAS FILE=DTDATA,SUFFIX=FIX,$
-WRITE DTMAS SEGNAME=SEG1,$
-WRITE DTMAS FIELD=DT,DT,A8,A8,$
-WRITE DTMAS DEFINE DTYYMDI/I8YYMD=EDIT(DT);$
-WRITE DTMAS DEFINE DTYYMDX/YYMD=DATECVT(DTYYMDI, 'I8YYMD', 'YYMD');$
-WRITE DTMAS DEFINE DTYYMDS/HYYMDS=HDTTM(DTYYMDX,8,'HYYMDS');$
-RUN
TABLE FILE DTDATA
PRINT
DTYYMDS AS 'DT'
-*ON TABLE SET ASNAMES ON
-*ON TABLE HOLD AS DTLOADFILE
END

This message has been edited. Last edited by: <Kathryn Henning>,

Just an idea. To fix "ORA-01795: maximum number of expressions in a list is 1000" error, instead of passing more than 1000 values in a single variable, use JS to break the variable into multiple variables and in Oracle use OR clause to include all the selected values in filter.

For TIMESCALE_DT variable, break it into TIMESCALE_DT_1, TIMESCALE_DT_2,etc based on number of selections.
Then use WHERE (TIME_SCALE_DATE IN ('&TIMESCALE_DT_1') OR TIME_SCALE_DATE IN ('&TIMESCALE_DT_2') OR TIME_SCALE_DATE IN ('&TIMESCALE_DT_3') ...)

Hope this helps.

Thanks,
Ram

Thanks Ram.
I'll look into this.

Follow-up: I noticed that the issue is really 4096 bytes not 1000 values, in this instance.
Ram: your suggestion is still a possible solution which I'll try.