Focal Point
[CLOSED] Using Hold File in a Where statement

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/5171087331

June 29, 2004, 02:42 PM
reFOCUSing
[CLOSED] Using Hold File in a Where statement
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>,
June 29, 2004, 03:01 PM
TerryW
Yes

Be a little more specific as to what you're trying to do and you can get a more specific answer.
June 29, 2004, 03:02 PM
<Pietro De Santis>
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
July 01, 2004, 10:57 AM
Tony A
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
July 02, 2004, 03:57 PM
reFOCUSing
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);
July 02, 2004, 04:47 PM
Leah
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?
July 14, 2004, 09:11 PM
reFOCUSing
Yes I have try that and it did not work.
July 15, 2004, 12:28 PM
susannah
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.
April 12, 2007, 10:20 AM
cdprasad
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.
April 12, 2007, 10:44 AM
susannah
cd,,you have to replace 'myholdfile' with the actual name of your holdfile




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
April 12, 2007, 11:39 AM
Alan B
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?


Alan.
WF 7.705/8.007
April 12, 2007, 11:50 AM
JimRice
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


WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
April 12, 2007, 12:46 PM
susannah
ah. of course. jim's so right. i'm just wishfully thinking.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
April 12, 2007, 01:30 PM
FrankDutch
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

April 12, 2007, 01:52 PM
JimRice
Frank,

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

Jim


WF DevStu 5.2.6/WF Srv 5.2.4/Win NT 5.2
April 12, 2007, 04:53 PM
Stan
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
April 12, 2007, 05:13 PM
Alan B
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.


Alan.
WF 7.705/8.007
October 21, 2015, 09:28 AM
Ricardo Augusto
Did the syntax changed?

I am getting 0 records while using this:

IF FIELD EQ (HOLD)


WebFOCUS 8.1.05 / APP Studio
October 21, 2015, 09:37 AM
Ricardo Augusto
I solved it using a LTRIM(RTRIM(FIELD)); before creating the hold.

thanks


WebFOCUS 8.1.05 / APP Studio