Is there a way to use the contents of a hold file in a where on another table?

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

Yes

Be a little more specific as to what you're trying to do and you can get a more specific answer.

If the hold file contains only one column, you can do:

WHERE APPLE_TYPE IN FILE HOLDFILE

or

WHERE NOT APPLE_TYPE IN FILE HOLDFILE

CurtisA,

You could use the decode function as in -

TABLE FILE EDUCFILE
PRINT EMP_ID
ON TABLE HOLD
END
DEFINE FILE EMPLOYEE
WANTED/I1 = DECODE EMP_ID(HOLD ELSE 1);
END
TABLE FILE EMPLOYEE
PRINT EMP_ID
LAST_NAME
FIRST_NAME
COMPUTE
WHERE WANTED NE 1 ;
END

Thanks.
I got this to work but I was having some problems using it in the following where statement. Does anyone know why?

WHERE
field1 EQ 'value1' OR
(field1 NE 'value1' AND field2 IN FILE hold1);

I've not used in WHERE ... IN, however, I have noticed with some selections you have to use complete parenthesis to get it to work correctly.

WHERE ((FIELD1 EQ 'VALUE') OR ((FIELD1 NE 'VALUE') AND (FIELD2 IN FILE HOLD)));

Have you tried that?

Yes I have try that and it did not work.

sometimes its easier (for me, anyway)to take a less elegant approach and define some flags. See if some variant of this might work:
DEFINE FILE myfile
FLAG1/I1=IF FIELD1 IS value1 THEN 1 ELSE 0;
FLAG2/I2=IF FIELD2 IS (myholdfile) THEN 1 ELSE 0;
then maybe
FLAG3/I2=FLAG1+FLAG3;
END
TABLE FILE myfile
IF FLAG3 NE 0
...
makes it easy to test each filter component incrementally, e.g. just to make sure your holdfile is readable and that filter alone is working ok.

FLAG2/I2=IF FIELD2 IS (myholdfile) THEN 1 ELSE 0;

This statement is not working

FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: myholdfile

This is the error it is giving.
I checked, the hold file is creating.

cd,,you have to replace 'myholdfile' with the actual name of your holdfile

Susannah
I would also get the same error using a file within a DEFINE using that syntax. I only use a file within a WHERE/IF now.
Am I doing something wrong?

Here's an example using the CAR file which shows using a file name in a define or in a where clause:
----------------------------------------------------------
DEFINE FILE CAR
CNT/I5SC = 1;
END
-*
TABLE FILE CAR
SUM CNT
BY COUNTRY
WHERE COUNTRY IN ('ITALY', 'JAPAN')
ON TABLE HOLD
END
-*
TABLE FILE HOLD
PRINT COUNTRY
ON TABLE HOLD AS HLD_COU
END
-******************************************
TABLE FILE CAR
SUM CNT
BY CAR
WHERE CAR IN ('JAGUAR', 'AUDI')
ON TABLE HOLD
END
-*
TABLE FILE HOLD
PRINT CAR
ON TABLE HOLD AS HLD_CAR
END
-******************************************
DEFINE FILE CAR
CHK_COU/I1 = DECODE COUNTRY (HLD_COU ELSE 1);
CHK_CAR/I1 = DECODE CAR (HLD_CAR ELSE 1);
END
-*
TABLE FILE CAR
PRINT CAR CHK_COU CHK_CAR
BY COUNTRY
WHERE (CHK_COU EQ 0) OR (CHK_CAR EQ 0)
WHERE (COUNTRY IN FILE HLD_COU) OR (CAR IN FILE HLD_CAR)
END
---------------------------------------------------------------------
Jim

ah. of course. jim's so right. i'm just wishfully thinking.

quote:

WHERE (CHK_COU EQ 0) OR (CHK_CAR EQ 0)
WHERE (COUNTRY IN FILE HLD_COU) OR (CAR IN FILE HLD_CAR)

Are these two lines not the same "where" in fact.

What is the advantage for one or the other?

Frank

Frank,

Yes, these "where" clauses do the same thing. I just put them both there to show each way.

Jim

I could possibly be missing the point here, but if it is just a single column of data that is no more than a certain size - I forget the exact limit, you can do something along the following lines:

Assume you have a file seats.dat with the following data:
2
4
6


FILEDEF SITZ DISK seats.dat
-RUN
DEFINE FILE CAR
ASEATS/A6 = EDIT(SEATS,'$$9');
END
-*
TABLE FILE CAR
PRINT ASEATS SEATS MODEL
WHERE ASEATS EQ '(SITZ)'
END

Stan

I don't think you are missing anything really. Jim was showing, nicely, different approaches to screening from a file. You can use
IF ASEATS EQ (SITZ)
- no quotes and not a WHERE. But if you use a WHERE, necessary for compound conditions, then the syntax is
WHERE ASEATS IN FILE SITZ;

This file can be circa 16k for WHERE and 32k for IF.

Did the syntax changed?

I am getting 0 records while using this:

IF FIELD EQ (HOLD)

I solved it using a LTRIM(RTRIM(FIELD)); before creating the hold.

thanks