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     [SOLVED] DEFINE a single variable inside a FEX after the JOIN
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] DEFINE a single variable inside a FEX after the JOIN
 Login/Join
 
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, 2011Reply With QuoteReport This Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6272 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
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, 2011Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
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: 87 | Location: MI | Registered: July 23, 2009Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
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: 87 | Location: MI | Registered: July 23, 2009Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

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

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