How to achieve the following scenario

IF ( V_EMP IS (EMP_FILE) ) OR ( V_VAR EQ '122' )

Here V_EMP and V_VAR are the two variables of table request
EMP_FILE is the hold file with the list of EMP NO

and also how to filter if we have more than one filter condition

eg: Suppose we had filter conditions on two hold files
like IF ( V_EMP1 IS (EMP_FILE1) ) AND ( V_EMP2 IS (EMP_FILE2) )

Can any body resolve this ....


Thanku
cdprasad

WF/7.14 WIN2000

Try replacing the IS with EQ.

Try

WHERE (V_EMP IN FILE EMP_FILE) OR (V_VAR EQ '122');

and

WHERE (V_EMP1 IN FILE EMP_FILE1) AND (V_EMP2 IN FILE EMP_FILE2);

Alan's suggestion should work fine. Here's another idea that overcomes a certain limitation. There is a limit to the number of values that can exists in the test file. So a simple JOIN could also resolve the problem. Even if the test files are just large but not over the limit a join may be more efficient, especially if the DBMS can do the join instead of FOCUS.

JOIN V_EMP IN hostfile TO crfield IN EMPFILE AS J0

In the first example you would also need to do a SET ALL=ON for a left outer join and then

WHERE crfield NE ('' or 0 or MISSING depending on format of crfield) OR V_VAR EQ '122'

In the second example a second join would also be used

JOIN V_EMP1 IN hostfile TO crfield1 IN EMP_FILE1 AS J0
JOIN V_EMP2 IN hostfile TO crfield2 IN EMP_FILE2 AS J1

If you use any reference to a field in EMP_FILE1 and EMP_FILE2 the inner join takes care of the selection criteria

TABLE FILE hostfile
PRINT
V_EMP
crfield1 NOPRINT
crfield2 NOPRINT
END

Thank u Alan and Darin lee

Alan method is working fine.

But when the size of the hold file grows the method fails i.e the limitation of where clause.

In that case we can go for the method like suggested by Darin Lee


Thanks & Regards,
cdprasad

I have a scenario where I need to filter against a hold file which can contain hundreds or even thousands of rows. The hold file was achieved in a somewhat complex manner, so a simple join wouldn't work for me.

I had a query against an Oracle table, filtered against a large hold file...

TABLE FILE XYZ
...
ON TABLE HOLD AS LARGEHOLD
END

TABLE FILE ORACLE_TABLE
SUM
FIELD
BY BYFIELD
IF FIELD2 EQ (LARGEHOLD)
ON TABLE HOLD AS RESULTS
END

I found that the performance was very slow...about 25 seconds to retrieve a few thousand records from a table with 1.75 million rows. It turns out that the resultant Oracle query gets bogged down with the {WHERE field IN list} clause (which is the result of the IF FIELD2 EQ (LARGEHOLD) statement). My list was about 135 entries.

So what I did was create a 2-step process. I moved the filter against LARGEHOLD to a second query, like this:

TABLE FILE ORACLE_TABLE
SUM
FIELD
BY BYFIELD
ON TABLE HOLD AS RESULTS
END

TABLE FILE RESULTS
SUM
FIELD
BY BYFIELD
IF FIELD2 EQ (LARGEHOLD)
ON TABLE HOLD AS FINAL
END

This had the effect of reducing response time to 1/5 of the original query. It went from 25 seconds to 5 seconds.

And as an added bonus, I don't hit the Oracle limit of 1000 expressions in a list (when my list happens to be larger than 1000 items), because the filter is now against the Focus file, which does not have this limitation.

Here is the error message I received when filtering directly against the Oracle table, with a list > 1000 items:
(FOC1400) SQLCODE IS 1795 (HEX: 00000703)
: ORA-01795: maximum number of expressions in a list is 1000

Hope this helps someone.
Sean