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

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
DEFINE v/s COMPUTE
 Login/Join
 
<Kalyan>
posted
hello,
I have a report which goes against a table having upto 10 million recs.
Now I had given a define on the table something like this:
DEFINE FILE MYTABLE
TOD/YYMD='&DATEYYMD';
END
TABLE FILE MYTABLE
PRINT
a
b
c

WHERE
(TOD-BIL_DT) GT 15 AND
(TOD -BIL_DT) LE 30
END

********************************
I modified the report above and removed the define and put it in a where total
**********************************
TABLE FILE MYTABLE
PRINT
a
b
c
COMPUTE TOD/YYMD='&DATEYYMD'
WHERE TOTAL
(TOD-BIL_DT) GT 15 AND
(TOD -BIL_DT) LE 30
END
************************************

Could anyone explain how the 2 cases above work? That is how does focus interpret and do the process of querying?
In the first case does focus actually do the define for each of the 10 million records :-(
 
Reply With QuoteReport This Post
Virtuoso
posted Hide Post
It depends (on the type of data source and indexing, for example). But why take chances? There is no need to introduce TOD as a DEFINE or COMPUTE variable.

Your requirement of
(TOD-BIL_DT) GT 15 AND (TOD -BIL_DT) LE 30
is equivalent to
BIL_DT GE (TOD-30) AND BIL_DT LT (TOD-15)

Since TOD is a constant (for the duration of the query), so are (TOD-30) and (TOD-15);
replace them with computed date strings based on the current date, as follows:

-* compute end-dates: [This is from memory; check the syntax for AYMD().]
-SET &DT15AGO = AYMD( &DATEYYMD, -15, 'I8');
-SET &DT30AGO = AYMD( &DATEYYMD, -30, 'I8');

-* query:
TABLE ...
WHERE (BIL_DT GE '&DT30AGO') AND (BIL_DT LT '&DT15AGO');
END

The &DTxxx references get resolved before the TABLE request is parsed, so it will be treated as (for example)
WHERE (BIL_DT GE '20050101') AND (BIL_DT LT '20050116');

-- which maximizes the chance for Focus to optimize the retrieval, since the condition is expressed directly in term of a database column.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
short answer: yes, define creates a virtual variable for every record. Compute calculates a variable on the aggregate, the final reporting dataset you've requested.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 


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