Here's my question:

How can I pass multiple values in SQL pass thru in the "in clause" by retreiving values from focus file or hold files?

As the database is very huge, we need to pass these values and retrieve the data.We cannot create a focus mart/hold file from SQL pass thru and then apply filter values.

I tried creating a string from the values obtained and then passed it in the IN CLAUSE, where as I have a scenario,that values are exceeding 4000,and these values are dynamically generated.

Any suggestions would be most welcome?

This message has been edited. Last edited by: Kerry,

Divya,

When you say exceeding 4000, do you mean the number of literals or total string length.
I think we have limit on the maximum values in Oracle "IN Clause" to 1000 literals. So anything exceeding this will not be supported by oracle first.

The limit is 1000.

I would suggest creating a temporary oracle table, and joining to it in the SQL.

Hi, Yes I know this is not supported, Thats why I need a work around to pass these 4000 values,
To answer your question, The no. of values is 4000, not the the string length...

Thanks Waz,

Will try doing that!

You could also try splitting your clause in multiple WHERE .. IN .. chunks, keeping each of them below the 1,000 limit.

SQL SQLORA
SELECT blah FROM mytable
WHERE (code IN (<first set of elements>)
   OR code IN (<next set elements>)
   OR code IN (<next set elements>)
   OR ...
)
END

To create those lines dynamically, you will HOLD your list of values (HOLD ALPHA) and use Dialog Manager to build amper variables for each list. You will finally reference those variables in your SQL passthru ...

TABLE FILE FILTER_VALUES
PRINT FILTER_VAL
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HFILTERS FORMAT ALPHA
END
-RUN
-* Dialog Manager magic here!!!
-* ......
-*
-* Build your query
SQL SQLORA
SELECT blah FROM mytable
WHERE (
      code IN (&FILTER1)
   OR code IN (&FILTER2)
   OR code IN (&FILTER3)
   OR ...
)
END

What Waz suggested to do is, in my opinion, the best and most efficient way to accomplish what you need.

There are however situations where you need a quick solution and in some environments it may take the "Production" guys a rather long time to promote any changes to the database (even if temporary) and it may not be ready in a timely fashion ... yeah, I'm that impatient! so in cases like those I prefer to keep my changes within the realm of WebFOCUS provided performance does not get significantly impacted.

Divya, here's a sample code using the car table illustrating the idea I expressed above.

For the purpose of the example, I am creating 5 amper variables (&FILTER1, &FILTER2, ...) each of them containing a string of up to 5 comma-separated values (read from CAR.MODEL) that will be finally passed to the IN clause in SQL.

TABLE FILE CAR
PRINT MODEL AS 'FILTER_VAL'
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HFILTERS FORMAT ALPHA
END
-RUN

-SET &FILTER_CNT=&LINES;

-SET &FILTER1 = 'FOC_NONE';
-SET &FILTER2 = 'FOC_NONE';
-SET &FILTER3 = 'FOC_NONE';
-SET &FILTER4 = 'FOC_NONE';
-SET &FILTER5 = 'FOC_NONE';
-*
-SET &ITM_NUM = 0;
-SET &SET_NUM = 0;
-SET &SET_SIZE= 5;
-*
-* Loop through the filter values and build &FILTERn expressions
-* with sets of up to 5 values (or whatever &SET_SIZE says!)
-*
-REPEAT :READFILTER &FILTER_CNT TIMES;
-READFILE HFILTERS
-SET &ITM_NUM = IF &ITM_NUM LT &SET_SIZE THEN &ITM_NUM + 1 ELSE 1;
-SET &SET_NUM = IF &ITM_NUM EQ 1 THEN &SET_NUM + 1 ELSE &SET_NUM;
-SET &FILTER_VAL = TRUNCATE(&FILTER_VAL);

-SET &FILTER.&SET_NUM = IF &FILTER.&SET_NUM EQ 'FOC_NONE' THEN '''' || &FILTER_VAL.QUOTEDSTRING || ''''
-                       ELSE &FILTER.&SET_NUM || ',' || '''' || &FILTER_VAL.QUOTEDSTRING || '''';
-:READFILTER
-*
-* Build SQL query 
-SET &ECHO=ON;
SQL
SELECT COUNTRY, CAR, MODEL, SEATS FROM CAR
WHERE (
    MODEL IN (&FILTER1)
 OR MODEL IN (&FILTER2)
 OR MODEL IN (&FILTER3)
 OR MODEL IN (&FILTER4)
 OR MODEL IN (&FILTER5)
)
END

Based on the data available in the CAR table, we end up with the following "filters":

FILTER1 => 'V12XKE AUTO','XJ12L AUTO','INTERCEPTOR III','B210 2 DOOR AUTO','DORA 2 DOOR'
FILTER2 => 'COROLLA 4 DOOR DIX AUTO','100 LS 2 DOOR AUTO','TR7','2000 4 DOOR BERLINA','2000 SPIDER VELOCE'
FILTER3 => '2000 GT VELOCE','2002 2 DOOR','2002 2 DOOR AUTO','530I 4 DOOR','530I 4 DOOR AUTO'
FILTER4 => '3.0 SI 4 DOOR','3.0 SI 4 DOOR AUTO','504 4 DOOR'
FILTER5 => FOC_NONE

Sticking them inside of SQL as: WHERE field IN (&FILTER1) will be resolved nicely by Dialog Manager and those with a value of FOC_NONE will naturally be removed from the request altogether.

Hope this helps.

Hi, Thanks for the reply, I really appreciate the pain of giving me the block of code too!

Thanks again.....

You're welcome! I love working on nice challenges like this every once in a while ... who knows? I might have a need for something like this later on so I may have saved myself some effort down the road

This message has been edited. Last edited by: njsden,