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     [SOLVED] DEFINE a single variable inside a FEX after the JOIN

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] DEFINE a single variable inside a FEX after the JOIN
 Login/Join
 
ewinery
Member
posted
I have a focus executable I am trying to write where I create a new field to be used on the fly. I thought i could simply add a "DEFINE" in line after the joins of tables then report on that new field.

The new field has to be defined after the joins because the logic uses data across the combined tables so it is not defined in the master files.

A COMPUTE will not work because I need to use the field in the WHERE clause.

Example:
  
 
 ...

 JOIN
 LEFT_OUTER CLUBMEMBERS.CLUBMEMBERS.MEMBERID IN CLUBMEMBERS TO UNIQUE
 CLUBSWITCHES.CLUBSWITCHES.MEMBERID IN CLUBSWITCHES TAG J8 AS J8
 END
 
 -* Define Something new
 DEFINE SOMETHING/A1V=
 IF CLUBMEMBERS.CLUBMEMBERS.VALUE EQ 1 THEN '1' ELSE
 IF J8.CLUBSWITCHES.VALUE EQ '2' THEN '2' ELSE
 '3';
 END
 
 TABLE FILE CLUBMEMBERS
 
 PRINT SOMETHING
 
 WHERE SOMETHING EQ &PARAM

This message has been edited. Last edited by: Kerry,


WebFOCUS Version: 7703
Windows, all output
 
Posts: 16 | Registered: August 18, 2011Report This Post
Waz
Expert
posted Hide Post
I think you need to review the manual on DEFINE.

It should be
DEFINE FILE CLUBMEMBERS
 SOMETHING...
END
.
.
.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
ABT
Master
posted Hide Post
WHEREs that refer to defined fields are slow. at least that's what our internal Best Coding Practices documentation says.


------------------------------------
WF Environment:
------------------------------------
Server/Client, ReportCaster, Dev Studio: 7.6.11
Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist
OS: Windows Server 2003
Application/Web Server: Tomcat 5.5.25
Java: JDK 1.6.0_03
Authentication: LDAP, MRREALM Driver
Output: PDF, EXL2K, HTM

------------------------------------
Databases:
------------------------------------
Oracle 10g
DB2 (AS/400)
MSSQL Server 2005
Access/FoxPro
 
Posts: 561 | Registered: February 03, 2010Report This Post
ewinery
Member
posted Hide Post
Yup, missed the FILE in there and fixed the issue.


WebFOCUS Version: 7703
Windows, all output
 
Posts: 16 | Registered: August 18, 2011Report This Post
susannah
Expert
posted Hide Post
and if you're comparing to a value (rather than a field), use IF , not WHERE.
wicked faster.
IF LUCY GT 1
WHERE LUCY GT RICKY



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
MathematicalRob
Gold member
posted Hide Post
quote:
Originally posted by susannah:
and if you're comparing to a value (rather than a field), use IF , not WHERE.
wicked faster.
IF LUCY GT 1
WHERE LUCY GT RICKY


Susannah, I found your note intriguing, so I thought I would try it. I alternated using IF and WHERE with the following filters on about 630k records and a runtime around 30 sec.

WHERE RCRAPP4_EMAIL_ADDRESS GE 'P'
WHERE RCRAPP4_EMAIL_ADDRESS LT 'Q'
WHERE RCRAPP4_P_EMAIL_ADDRESS GE 'P'
WHERE RCRAPP4_P_EMAIL_ADDRESS LT 'Q'


My original data source is an Oracle database, so after going against that and seeing nothing interesting, I held the data to a Focus file and then ran the same filters again. In my limited tests, the WHERE statement came out slightly ahead. . . So now I'm curious whether there are more unique circumstances where a gain in performance can be realized?

This message has been edited. Last edited by: MathematicalRob,


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 2009Report This Post
FrankDutch
Virtuoso
posted Hide Post
I wonder how you measure this.
A big part of the time between the start of the processing and getting the result might be consumed by processing the result to the screen, the webbrowser you use, the memory of the pc and several other circumstances.
I am not sure if there is a real prove of sussanahs statement other then the many many years of experience she has.




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
susannah
Expert
posted Hide Post
has zero to do with experience
has to do with definition.
IF invokes a simple process
WHERE invokes a bucketload of stuff;
ibi will explain it to you.
its definitional, not empirical.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
MathematicalRob
Gold member
posted Hide Post
I see; that makes sense - thank you!

I have a little timing routine that I wrap around my production reports so I can log runtime, so I pressed that into service for my crude attempt at experimentation.

Didn't mean to be difficult; I just like fast code, so I thought I'd try it out since I've always ignored "IF". . .

Thanks again!
Rob


WebFocus 8201m on Windows; App Studio 8201; Procedures: WebFocus with SQL; HTML Parameter Pages & Dashboard; Output: Excel, HTML, & PDF.
 
Posts: 88 | Location: MI | Registered: July 23, 2009Report This Post
Dan Satchell
Virtuoso
posted Hide Post
quote:
A COMPUTE will not work because I need to use the field in the WHERE clause.

On the contrary, a COMPUTE can be used in a WHERE clause if you use the correct syntax:

WHERE TOTAL SOMETHING EQ '&PARAM';


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Waz
Expert
posted Hide Post
quote:
and if you're comparing to a value (rather than a field), use IF , not WHERE.
wicked faster.
IF LUCY GT 1
WHERE LUCY GT RICKY


If someone in IBI is checking this thread, I'd like to know if this is still the case.

We are after any ways to speed up all our reports.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | 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     [SOLVED] DEFINE a single variable inside a FEX after the JOIN

Copyright © 1996-2020 Information Builders