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.