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,

I think you need to review the manual on DEFINE.

It should be

DEFINE FILE CLUBMEMBERS
 SOMETHING...
END
.
.
.
  

WHEREs that refer to defined fields are slow. at least that's what our internal Best Coding Practices documentation says.

Yup, missed the FILE in there and fixed the issue.

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

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,

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.

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.

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

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';

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.