![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Guru |
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>, | ||
|
Silver Member |
Yes Be a little more specific as to what you're trying to do and you can get a more specific answer. | |||
|
<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 | ||
|
Expert |
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 | |||
|
Guru |
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); | |||
|
Virtuoso |
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? | |||
|
Guru |
Yes I have try that and it did not work. | |||
|
Expert |
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. | |||
|
Member |
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. | |||
|
Expert |
cd,,you have to replace 'myholdfile' with the actual name of your holdfile
| |||||
|
Virtuoso |
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 | |||
|
Platinum Member |
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 | |||
|
Expert |
ah. of course. jim's so right. i'm just wishfully thinking.
| |||||
|
Virtuoso |
Are these two lines not the same "where" in fact. What is the advantage for one or the other? Frank
| |||||||
|
Platinum Member |
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 | |||
|
Gold member |
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 | |||
|
Virtuoso |
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 | |||
|
Guru |
Did the syntax changed? I am getting 0 records while using this: IF FIELD EQ (HOLD) WebFOCUS 8.1.05 / APP Studio | |||
|
Guru |
I solved it using a LTRIM(RTRIM(FIELD)); before creating the hold. thanks WebFOCUS 8.1.05 / APP Studio | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|