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     How do I create a subset of a table?
Page 1 2 

Read-Only Read-Only Topic
Go
Search
Notify
Tools
How do I create a subset of a table?
 Login/Join
 
Silver Member
posted
Hi Friends,

I have got a requirement where in I need to retrieve only current year's data from a table which is having morethan 15 years of data.

When I just query that table, it takes a very long(15 mins and above) to run and finally timesout.

May I know if it is possible to just have current year's data in a temporary table and keep refreshing it on a regular basis and use that file to query current year's data rather than hitting the entire table's data?

Appreciate any assistance.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Expert
posted Hide Post
have your dba's create you a materialized view of current year, and perhaps prior year, and refresh it ever night. let them worry about it.
or ...
APP HOLD myapp
TABLE FILE yourfile
IF YEAR IS &DATEYY
PRINT fields
BY datefield
ON TABLE HOLD AS thisyear FORMAT FOCUS INDEX DATEFIELD
END
APP HOLD
.. and put this job into report caster and run it every night. Where are you located?




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
Silver Member
posted Hide Post
yes. you can create a report caster schedule to load the current year data from the master table in to a webfocus temporary table on daily basis and use the temporary table in your report.


Webfocus 7.1.6, Webfocus 7.7, Webfocus 8
 
Posts: 33 | Registered: August 16, 2012Report This Post
Expert
posted Hide Post
Have you checked to see if the table is properly indexed, so retrieval of a specified year is efficient ?


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
Platinum Member
posted Hide Post
First, isn't WHERE usually recommended over IF for selection? Second, I have found that constructing a WHERE clause using the date format matching the table's date format greatly increases the chance that the selection is done on the data server (SQL in our case) rather than inside of Focus. The data server tends to be more effiricient at this. Third, we have found that sometimes the statistics on the table need to be regenerated more frequesntly. This is a DBA task that makes the indexes work better.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
 
Posts: 103 | Registered: April 27, 2011Report This Post
Silver Member
posted Hide Post
Susannah,

Since I am using the IDMS tables for reporting,
is it that my IDMS admin would be able to help me out in creating materialized view of current year in IDMS?

Since I have never used APP HOLD myapp before, I would like to know what should "myapp" refer to in "APP HOLD myapp"? Or is it just a Keyword?

Thanks in advance
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Rakesh,

Do you mean to suggest the same solution as Susannah mentioned above? Or do you mean to say that I just create a HOLD file with current year's data and schedule it in Report Caster on a daily basis? If yes, willn't the schedule take a long time to run and affect the server's performance?

If none of the above is not what you mean, can you please detail.

Thanks in advance
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Waz & Kevin,

I am using the IDMS tables for reporting and they were already created years ago and they are not indexed.

When I asked the IDMS admin to add the indices to the already existing tables, he said that creating indicies on already existing tables cannot be done and will impact the already existing reports.

In short, the IDMS admin is not ready to create indices in already existing masters.

If possible, please advice me some alternate way which doesnt involve admin.

Thanks
Teju

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Susannah,

Also, is it the hold file name that I should use in my original report coding?

I am located in Maryland.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Expert
posted Hide Post
APP HOLD resets the hold directory to your agent
APP HOLD someapp sets the hold directory to your app. but you want to turn that off as soon as your file and its master have been parked in your app; that's what the APP HOLD command does...turns it off.




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
Expert
posted Hide Post
nope
whenever there is a constant, use IF
IF has no overhead
WHERE has a bucketload of overhead




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
Expert
posted Hide Post
whenever you want to write a report, you'll TABLE FILE against whatever extract file you've created .
be sure you call it something very identifiable.




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
Silver Member
posted Hide Post
Susannah,

As suggested by you, I just created a fex file and scheduled it to run now.

Will see how it works and would get back to you if I have any more questions and need any help regarding this.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Platinum Member
posted Hide Post
I have been touting the advantage/efficiency of IF over WHERE for years and glad to see Susannah that you addressed this question!

Of course, WHERE is necessary in certain conditions where the IF does not work.


Vivian


Vivian Perlmutter
Aviter, Inc.


WebFOCUS Keysheet Rel. 8.0.2
(Almost) 1001 Ways to Work with Dates thru Rel. 8.0.2
Focus since 1982
WebFOCUS since the beginning
Vivian@aviter.com

 
Posts: 191 | Location: Henderson, Nevada | Registered: April 29, 2003Report This Post
Silver Member
posted Hide Post
Hi Susannah,

I just happened to run the schedule and after 3 hours of running, it gave the below message in email format

BTP1010 Schedule Executed Due To NEXTRUNTIME at cob-wfocusp-01.wssc.ad.root:8200
BTP1020 Starting task: billspassthru
BTP1020 Task type: MR Standard Report
BTP1020 Task domain: wsscprod/wsscprod.htm
BTP1020 Retrieving MR report: app/passthru
BTP1020 Connecting to server WSSCPROD with execution id webfocp
BTP1020 Executing focexec.
BTP1020 No report to create.
BTP1020 OUTPUT FILE NOT ALLOCATED: ACCVAL
BTP1020 Task finished.
BTP1010 No report to distribute.


The code I have used in my fex file is as follows:

APP HOLD MMIS
DEFINE FILE CSBILLCH
TYPEBILL/A1=EDIT(I2586_BILL_TYPE_CODE,'9$$');
BILL/A3=DECODE TYPEBILL(1 'ACT' 2 'EST' 3 'CUS' 4 'ADJ' 5 'PAY'
6 'IDC' 7 'MIS' 8 'REF' 9 'FRS');
YY/A2 = EDIT(I2582_BILL_GEN_DATE,'99$$$$');
MM/A2 = EDIT(I2582_BILL_GEN_DATE,'$$99$$');
NY/I3 = EDIT(YY);
NM/I3 = EDIT(MM);
CRAPFY/I3 = IF NM GE 07 THEN NY + 1 ELSE NY;
FY/A2 = EDIT(CRAPFY,'$99');
END
TABLE FILE CSBILLCH
COUNT I2511_CODE_ID_CSACCT/I9 AS 'ACCT1'
BY FY
BY MM
BY I2582_BILL_GEN_DATE
IF I2586_BILL_TYPE_CODE NE 5$$
-*ACROSS BILL AS ''
IF FY EQ 14
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS ACCVAL FORMAT FOCUS INDEX I2582_BILL_GEN_DATE
END

APP HOLD


Please advice on what's wrong with my code.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Expert
posted Hide Post
try running something very small, that has only 10 or so lines in it
IF RECORDLIMIT IS 10
and get your mechanics down first.




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
Expert
posted Hide Post
What ever path you use, I think you need to understand the IDMS table and what is indexed.

Perhaps there are other ways to get this data out utilising the struncture that is currently there.

Perhaps you could also consider non FOCUS methods to extract the data.


Also, I think your admin is a bit lazy.


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
Virtuoso
posted Hide Post
quote:
ON TABLE HOLD AS ACCVAL FORMAT FOCUS INDEX I2582_BILL_GEN_DATE

When indexing a field in format FOCUS, there is a restriction on the length of the name (12 characters IIRC).

Either give the date field a shorter name, or use FORMAT XFOCUS.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Platinum Member
posted Hide Post
From your IF statements your only selections are on a wildcard and a defined field, this is going to impact the speed at which your data is returned.

Can you change your IF statements to use the underlying fields instead?

I'm assuming this line of code is to exclude all I2586_BILL_TYPE_CODE that start with a 5:

IF I2586_BILL_TYPE_CODE NE 5$$

have you tried

WHERE (I2586_BILL_TYPE_CODE LT '500') OR (I2586_BILL_TYPE_CODE GE '600');

to see if it runs any faster?

Similarly with your FY selection, can you use the database date field instead?


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
<FreSte>
posted
I think it is good to know how your TABLE-request is translated to SQL.
With all the EDIT's in the DEFINE I wonder if this will be translated to efficient SQL.

Put these statements just before your TABLE request. The request will not be
executed; just a SQL-statement will be generated.


SET TRACEOFF = ALL
SET TRACEUSER = ON
SET TRACEWRAP = 132
SET TRACEON = STMTRACE//CLIENT
SET TRACEON = SQLAGGR//CLIENT
SET TRACESTAMP = OFF
SET XRETRIEVAL = OFF
 
Report This Post
Platinum Member
posted Hide Post
Then I will use IF more than WHERE going forward. I trust the experts from FocalPoint more than the documentation. The documentation that says (note the Tip): Release 7.7.03 > Reporting Language > Creating Reports With WebFOCUS Language > Selecting Records for Your Report
Selecting Records Using IF Phrases
How to: Select Records Using the IF Phrase

The IF phrase selects records to be included in a report, and offers a subset of the functionality of WHERE. For a list of supported IF operators, see Using Operators in Record Selection Tests.

Tip: Unless you specifically require IF syntax (for example, to support legacy applications), we recommend using WHERE.


WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
 
Posts: 103 | Registered: April 27, 2011Report This Post
Expert
posted Hide Post
and we would recommend just the opposite.
IF works when comparing a field to a constant
WHERE is needed for anything more complicated eg field to field, field to expression, testing against computed field values, etc




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
Virtuoso
posted Hide Post
susannah --

From what I have observed, the TABLE parser in effect converts WHERE to IF when that is possible
-- i.e., it consistently treats
   WHERE field <op> constant;
and
   IF field <op> constant

identically (in terms of generated SQL, and internal processing for local files).

Do you have a counter-example?


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Silver Member
posted Hide Post
Sussanah,

The RC job ran well but when I queried the hold file ACCVAL as below, I got the error message

0 ERROR AT OR NEAR LINE 8 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC036) NO DATA FOUND FOR THE FOCUS FILE NAMED: ACCVAL


TABLE FILE ACCVAL
PRINT
*
WHERE RECORDLIMIT EQ 10
END
-EXIT


I see the accval.mas file created with SUFFIX=XFOC. But I would like the master file to be created with the SUFFIX=IDMSR. Please let me know how to go about it?

Thanks in advance

Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Hi FreSte,

I have already tried turning trace on 2 months back and was warned by my admin not to use it any more because it consumes lot of memory and is hitting the performance of the server.

But turning traces on do help a lot.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Hi Tewy,

I haven't tried using WHERE but I would try doing that now. I never thought that there is such huge difference between WHERE and IF.

Anyways thanks a lot for your valuble suggestion.

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Silver Member
posted Hide Post
Waz,

Can you please suggest any non-FOCUS method in which I can extract the data?

Thanks
Teju


Product & Release: WebFOCUS 7.7.03
Op. Sys: Linux for x64 64 bit
O/P formats: HTML & PDF
 
Posts: 35 | Registered: January 24, 2013Report This Post
Guru
posted Hide Post
You will definitely want to use tracing from time to time. However you will want to only turn it on as you are using it. There is a lot of data that can accumulate over time when tracing is on. You can also clean up the trace files after you get what you need.


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Report This Post
Master
posted Hide Post
Sheesh... All those years I was using IF and then I took some WF courses from IBI and they recommended WHERE except in defines and computes.... grrrrrrrrrrr


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Expert
posted Hide Post
constants in both examples.
you're missing the point




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
  Powered by Social Strata Page 1 2  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     How do I create a subset of a table?

Copyright © 1996-2020 Information Builders