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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
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 :-(
 
Report This Post
j.gross
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, 2005Report This Post
susannah
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, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders