Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using Hold File in a Where statement
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Using Hold File in a Where statement
 Login/Join
 
Guru
posted
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>,
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Silver Member
posted Hide Post
Yes

Be a little more specific as to what you're trying to do and you can get a more specific answer.
 
Posts: 40 | Registered: March 10, 2004Reply With QuoteReport This Post
<Pietro De Santis>
posted
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
 
Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 5610 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
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);
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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?
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Guru
posted Hide Post
Yes I have try that and it did not work.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 20 | Registered: December 21, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 118 | Location: Lincoln Nebraska | Registered: May 04, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 118 | Location: Lincoln Nebraska | Registered: May 04, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
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
 
Posts: 90 | Registered: April 15, 2004Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Guru
posted Hide Post
Did the syntax changed?

I am getting 0 records while using this:

IF FIELD EQ (HOLD)


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
Guru
posted Hide Post
I solved it using a LTRIM(RTRIM(FIELD)); before creating the hold.

thanks


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Using Hold File in a Where statement

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.