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     [SOLVED]Problem with FILEDEF and WRITE command

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Problem with FILEDEF and WRITE command
 Login/Join
 
Guru
posted
HI,
I am trying to read distinct COUNTRY values from CAR file one by one in a loop using below code:

-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
PRINT
DST.COUNTRY 
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS


-REPEAT LP &RECS TIMES

-READ HLD &COUNTRY.A50.
-TYPE &COUNTRY
-*
-SET &PARAM2 = TRUNCATE('&COUNTRY.EVAL');
-TYPE &PARAM2

FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT
-RUN
-WRITE BURSTPARAM &COUNTRY
-LP
  



I have to write the country value in BURSTPARAM .TXT and read this value in other procedure in loop as shown in above code.
When I am trying to WRITE the value in the file BURSTPARAM.TXT using FILEDEF , it only picks first value from the hold file HLD for each iteration.

When I run this code after removing FILEDEF and WRITE command, it runs for each of the country code.
Also, is there any timing issue for each iteration. i.e if second proc picks value ENGLAND and in the meantime the BURSTPARAM.TXT get overwritten and it may cause

Please help me understand the behaviour and cause of this issue.

Thanks.

This message has been edited. Last edited by: <Emily McAllister>,


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Virtuoso
posted Hide Post
Try this
-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS

FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT
-RUN

-REPEAT LP &RECS TIMES
-READFILE HLD
-TYPE &COUNTRY
-SET &PARAM2 = TRUNCATE('&COUNTRY.EVAL');
-TYPE &PARAM2

-WRITE BURSTPARAM &COUNTRY
FILEDEF BURSTPARAM DISK ABC/BURSTPARAM.TXT (APPEND
-RUN
-LP

The first FILEDEF clear the file, the second insure to APPEND data to it. Having the FILEDEF as you did always clear the file before each WRITE.
Also, using READFILE instead of READ allows you to discard the field length and with your BY and PRINT, your result in HLD id two columns, not one.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
quote:
COUNTRY

Hi Martin,
Thanks for your reply.
As this logic creates a file like below:

ENGLAND
FRANCE
ITALY
JAPAN
W GERMANY

The problem is that I wont be able to identify which value to pick in my second proc which basically uses only one value used in that iteration.
i.e For ENGLAND iteration the second proc will use ENGLAND in its logic. similarly for others.

Please suggest.

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Virtuoso
posted Hide Post
Hi Shankar,

Maybe it's me but I don't understand what you are looking for...

What do you mean by "For ENGLAND iteration the second proc will use ENGLAND in its logic" ?

Is that you want to execute another fex (or TABLE FILE) looping on the values contained in the FILEDEF ?
So meaning that you'll loop five times (one time for each COUNTRY values) ?


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Virtuoso
posted Hide Post
To grab every individual record from a held data request, you could do something similar to the following. Add a record counter to the end of the DM variables you're trying to create and store values in. Then you end up with DM variables that hold every value for every field from every record that comes back from the above data request. This can be a handy way to gather values from a data set to use later in custom HTML for widgets, etc. you might like to build for a dashboard/portal, etc. Hope this helps.

... Data request...
ON TABLE HOLD
...


-SET &I = 0;
-LOOP
-SET &I = &I + 1;
-IF &I GT 5 GOTO OUT;
-READ HOLD &FIELDONE.&I.A35. &FIELDTWO.&I.A35. &FIELDTHREE.&I.A35. &FIELDFOUR.&I.A35.
-TYPE &FIELDONE.&I &FIELDTWO.&I &FIELDTHREE.&I &FIELDFOUR.&I
-GOTO LOOP
-OUT


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Guru
posted Hide Post
quote:
something

Hi,
Yes Martin. I want to execute another fex by looping five times for each country values. It will read from FILEDEF file created to hold COUNTRY values.
I was thinking to overwrite the COUNTRY values for each iteration so that each iteration will pick the latest value to execute the other fex file.

I think overwriting the values in FILEDEF file will help here.
Please suggest.

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Virtuoso
posted Hide Post
No need to use FILEDEF for that
-SET &ECHO = ON;
-SET &ECHO = ALL;

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS HLD
END
-RUN

-SET &RECS=&LINES;
-TYPE &RECS

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
-READFILE HLD
-TYPE &COUNTRY

TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END
-RUN

-IF &I LT &RECS THEN GOTO NEXTCNTRY;


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Hi Martin,
Actually, the FILEDEF file country values will be used in the fex which is called through RC Schedule.
The parent fex (where the COUNTRY values is identified and written into FILEDEF) will then trigger this RC jobs which will execute the child fex which would use the parameter value in FILEDEF file.

It is just a way to pass parameter from one task to other in loop for each COUNTRY values.
Please advise.

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Virtuoso
posted Hide Post
Try this:
APP HOLD BASEAPP
END

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS BURSTPARAM FORMAT ALPHA
END
-RUN

-SET &RECS=&LINES;

APP HOLD
END

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
FILEDEF BURSTPARAM DISK baseapp/burstparam.ftm
-READFILE BURSTPARAM

TABLE FILE CAR
PRINT RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END

-IF &I LT &RECS THEN GOTO NEXTCNTRY;

Pay attention to not put any -RUN after the READFILE neither the TABLE FILE...END of the loop otherwise you will have five times the ENGLAND report : &COUNTRY will remain the same. In other words, do not put any -RUN in the loop.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
I have a fex - nearly 900 lines long - that does what you want - exactly along the lines that Martin has suggested. In my case I needed to create a spreadsheet with tabs for months along the bottom, so I couldn't use the first BY field as the burst parameter.

We still use ReportCaster to schedule the job, but EDAMAIL for the actual distribution.

Of course this method is somewhat inefficient - the entire fex gets run for each individual sales rep - but it works.

-INCLUDE FILEDEFS
-DEFAULTH &SALESREP=' ';
 
APP HOLD BASEAPP
 
-* Get a list of the sales reps and count how many their are
 
TABLE FILE SALES_TARGS
SUM SALESREP
BY SALESREP NOPRINT
ON TABLE HOLD AS ALLREPS FORMAT ALPHA
END
 
-SET &NUMREPS=&LINES;
-RUN
 
-READFILE ALLREPS
-* Note that READFILE converts the FOCUS fieldname to the Dialog Manager fieldname &SALESREP

-REPEAT ENDREPEAT &NUMREPS TIMES
 
-SET &SUBJECT='Targets and Sales for ' | &SALESREP | ' ' | &REPDATE;
 
-SET &SREP_EMAIL=LOCASE(3, &SALESREP, 'A3') || '@xxx.com';
 
-SET &ALL_EMAILS=&SREP_EMAIL || ';' | &ADMIN_EMAILS; 

-* 800+ lines of code in here... 

TABLE FILE XYZ
...
...
WHERE SALESREP EQ '&SALESREP'
END
 
EX EDAMAIL &ALL_EMAILS, ReportCaster@xxx.com, &SUBJECT,a,XLSX,baseapp/Targets and Sales for &SALESREP &REPDATE
-RUN
READFILE ALLREPS
ENDREPEAT
-RUN
 
TABLE FILE H001
SUM DUMMY NOPRINT
BY CCODE NOPRINT
HEADING
"Targets and Sales Reports completed"
END
 
-DONE

This message has been edited. Last edited by: George Patton,


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
Shankar, you gotta hold as FORMAT ALPHA
you're holding as a binary, so a -READ has a hard time. That's what a SAVE is, its a HOLD FORMAT ALPHA w/o the master.

plus you gotta put a NOCLOSE after your -READ so the fex doesn't keep going to the top of the extract file.




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
Guru
posted Hide Post
quote:
BURSTPARAM

Hi,
The parent and child tasks needs to be executed simultaneously. i.e. If Task1 uses ENGLAND value then task2 should also use ENGLAND value for that particular iteration.
Both the tasks are tightly coupled here.

I am just not able to understand, When Task1 loops through different country values and use that value for its execution, how can I pass the same value to Task2 to complete its execution simultaneously. Then it will move to next country value and execute both the tasks and similarly for all the country values.

Please suggest.

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Expert
posted Hide Post
There have been many suggestions. Please post the latest version of your code.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Virtuoso
posted Hide Post
You can put as many Task as you want in the loop
APP HOLD BASEAPP
END

TABLE FILE CAR
BY COUNTRY
ON TABLE HOLD AS BURSTPARAM FORMAT ALPHA
END
-RUN

-SET &RECS=&LINES;

APP HOLD
END

-SET &I = 0;
-NEXTCNTRY
-SET &I = &I + 1;
FILEDEF BURSTPARAM DISK baseapp/burstparam.ftm
-READFILE BURSTPARAM

-* TASK 1
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
WHERE COUNTRY EQ '&COUNTRY';
END

-* TASK 2
TABLE FILE CAR
SUM RETAIL_COST
BY COUNTRY
BY CAR
BY MODEL
WHERE COUNTRY EQ '&COUNTRY';
END

-IF &I LT &RECS THEN GOTO NEXTCNTRY;


You may need to be more clear if it doesn't answer your need


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Master
posted Hide Post
Martin is right (as usual): In my example I indicated that I have more than 800 lines of code inside the loop that reference all kinds of variables, but the main one is the &SALESREP that I use to pull data from several different sources and combine into a single report.


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
Virtuoso
posted Hide Post
Thanks George, but I'm not always right.
The fact is when I think that I may be wrong, I shut up Big Grin


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]Problem with FILEDEF and WRITE command

Copyright © 1996-2020 Information Builders