Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Using Hold File in a Where statement
 Login/Join
 
reFOCUSing
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, 2004Report This Post
TerryW
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, 2004Report 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
 
Report This Post
Tony A
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: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
reFOCUSing
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, 2004Report This Post
Leah
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, 2004Report This Post
reFOCUSing
Guru
posted Hide Post
Yes I have try that and it did not work.
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Report This Post
susannah
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, 2003Report This Post
cdprasad
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, 2006Report This Post
susannah
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, 2003Report This Post
Alan B
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, 2007Report This Post
JimRice
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, 2005Report This Post
susannah
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, 2003Report This Post
FrankDutch
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, 2006Report This Post
JimRice
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, 2005Report This Post
Stan
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, 2004Report This Post
Alan B
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, 2007Report This Post
Ricardo Augusto
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, 2006Report This Post
Ricardo Augusto
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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders