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>How to write data to a temp FOCUS table?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
<Solved>How to write data to a temp FOCUS table?
 Login/Join
 
Gold member
posted
I have 5 input variables sets from the user input screen, each set are like:
&From_OFC1, &To_OFC1, &From_num1, &To_num1,
&From_OFC2, &To_OFC2, &From_num2, &To_num2
&From_OFC3, &To_OFC3, &From_num3, &To_num3,
….

I want to create a temp FOCUS file to hold these input data, so I can SUM by OFC name , then I will join the temp FOCUS file with my main table on OFC name.
Can anyone help me? Thanks.

I am using WF8.0.9 with AppStudio on window.

This message has been edited. Last edited by: Emily Lee,


WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel
 
Posts: 61 | Registered: March 12, 2008Report This Post
Master
posted Hide Post
Search this forum for command "EDAPUT". Using this you can create on the fly master file. HOLD your input data in a file and using master file on top of it , you can easily convert to FOCUS format.
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Master
posted Hide Post
Ram's idea works, but is overkill IMHO.

Assuming you have some sort of database to report against I think all you have to do is
TABLE FILE XYZ
PRINT
REALFIELD NOPRINT
&From_OFC1
&To_OFC1
&From_num1
&To_num1
.. etc.
ON TABLE HOLD FORMAT FOCUS INDEX whatever ..
END

Now someone will correct me if I'm wrong (frequently). You may need to assign your amper variables to temporary fields using DEFINE in order to get proper field type and length.


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
For me the easiest approach is:
EX -LINES * EDAPUT MASTER,ofc,CV,MEM
FILENAME=ofc,SUFFIX=FOC,$
SEGNAME=OFC,SEGTYPE=,$
FIELD=From_OFC,,a24,$ 
FIELD=To_OFC,,a24,$ 
FIELD=From_num,,i10,$ 
FIELD=To_num,,i10,$
EDAPUT*

CREATE FILE ofc
 
MODIFY FILE ofc
FREEFORM From_OFC To_OFC From_num To_num
DATA
&From_OFC1, &To_OFC1, &From_num1, &To_num1,$
&From_OFC2, &To_OFC2, &From_num2, &To_num2,$
&From_OFC3, &To_OFC3, &From_num3, &To_num3,$
&From_OFC4, &To_OFC4, &From_num4, &To_num4,$
&From_OFC5, &To_OFC5, &From_num5, &To_num5,$
END

TABLE FILE ofc
PRINT *
END


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Master
posted Hide Post
Alan: Does that work?

You've set the format of your fields in the EDAPUT sequence, but how do you know what the incoming data actually looks like? Are the numbers integer or float, for example.

I suggested that a DEFINE along with HOLD FORMAT FOCUS might be the way to go so the data would be in a known format before the Focus database is created. For me, messing with / changing the field formats in a DEFINE is an easier approach.


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
quote:
Does that work?

George,

Yes, it does work and is a method that I also employ often - see replies to posts where sample data is required Wink

Knowing Alan's style of reply, I know that he has put the format prefixes in lower case to imply that these need to be thought about by the developer, after all we can only help the poster as much as the information they provide will allow. There is always (or should be Wink) an understanding that the full answer may require some thought by the individual!

I would agree though that, providing that Emily has a data source which she is happy to piggy-back then using a DEFINE approach will also work. I would also point out that if there are multiple rows required then the additional IF THEN ELSE or DECODEs required might be a tad tiresome!

For my money though, I would use the EDAPUT and MODIFY - but then I am very familiar with that sort of code!

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Master
posted Hide Post
Tony: I've seen your sample data examples many times of course and I use MODIFY all the time myself.

It's an interesting technique.

Emily could always piggy-back on CAR ! Smiler


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
Gold member
posted Hide Post
Thanks for all your helpful suggestions. I searched the EDAPUT, then I found Francis Mariani’s WRITE method in the following post. It is easy for me to understand, so I took that approach..

http://forums.informationbuild...091012803#8091012803

I made it worked. I can do TABLE print of the FOCUS hold file which has all my input lines. But I got stocked with new error, which took me two days, still don’t know why….. I try to use the new FOCUS HOLD file to join with the main focus file, it seems the office name never matched in the JOIN. I have carefully checked the office name spelling in my default values, with the office name in my main focus table. What could be wrong??? Thanks in advanced.

Here is my code ( simplified) …
 

-**  I have 4 set of input parameters, here I simply it with 2 sets

-DEFAULT &FROM_OFC1 = '03-BALTIMORE';
-DEFAULT &TO_OFC1       = '12-NHC BALTIMORE';
-DEFAULT &TRAN_NUM_1 = '10';

-DEFAULT &FROM_OFC2 = '03-BALTIMORE';
-DEFAULT &TO_OFC2      = '12-NHC CHICAGO';
-DEFAULT &TRAN_NUM_2 = '20';

-**** my main FOC table is in my working folder, so I place my temp foc in the same folder, it may not be necessary, but I try to play safe…..

APP HOLD  my working folder
-RUN

-**********************  use FILEDEF and WRITE ***************
-** Create master for TRANSFILE  *****

FILEDEF DATAMAST DISK TRANSFILE.MAS
-RUN

-WRITE DATAMAST FILE=TRANSFILE, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=TRAN_FLG, ALIAS=TRAN_FLG, USAGE=A1, ACTUAL=A1, $
-WRITE DATAMAST FIELD=TRAN_PEND, ALIAS=TRAN_PEND, USAGE=A4, ACTUAL=A4, $
-WRITE DATAMAST FIELD=OFFICE, ALIAS=OFFICE, USAGE=A45, ACTUAL=A45, $

-** Create data file for TRANSFILE  *****
-** I have trouble use WRITE to enter Integer,  so I define ‘TRAN_PEND’ as Alpha, then will convert it to INT.
-** the OFFICE name is various length, so I place it to the last.


FILEDEF TRANSFILE DISK TRANSFILE.FTM
-RUN
-WRITE TRANSFILE F  &TRAN_NUM_1   &FROM_OFC1
-WRITE TRANSFILE T  &TRAN_NUM_1   &TO_OFC1
-WRITE TRANSFILE F  &TRAN_NUM_2   &FROM_OFC2
-WRITE TRANSFILE T  &TRAN_NUM_2   &TO_OFC2
-RUN


DEFINE FILE TRANSFILE
CNT/I11 = EDIT(TRAN_PEND);
TRAN_CNT/I11 = IF TRAN_FLG EQ 'T' THEN CNT
          ELSE IF TRAN_FLG  EQ 'F' THEN 0 - CNT
		  ELSE 0;
END

-* before using  SUM command,  I found the hold the file need to have the INDEX 
-*
TABLE FILE TRANSFILE
PRINT
    TRAN_CNT
    TRAN_FLG
BY OFFICE
ON TABLE HOLD AS TRANSTEMP FORMAT FOCUS INDEX OFFICE
END
-RUN

-** Sum the input file with office to reduce multiple office rows
TABLE FILE TRANSTEMP
SUM
    TRAN_CNT
    TRAN_FLG
BY OFFICE
ON TABLE HOLD AS TRANSHOLD FORMAT FOCUS INDEX OFFICE
END
-RUN
*

-*************************************************************
-*JOIN TO EM_MAIN TABLE, both table should have indexed with OFFICE
-**********************************************************
JOIN CLEAR *
JOIN OFFICE   IN   EM_MAIN  TO OFFICE  IN TRANSHOLD AS J1
-RUN

DEFINE FILE EM_MAIN
NEW_TOTAL_PEND/I11 =  PENDING +  TRAN_CNT;
END

-*****Try to print office which has match rows,  that is also has TRAN_FLG EQ ‘T’ or ‘F’

TABLE FILE EM_MAIN
PRINT
       TRANS_FLG
       EM_MAIN.SEG01.OFFICE
       EM_MAIN.NEW_TOTAL_PEND 
 BY  EM_ MAIN.OFFICE
  WHERE TRAN_FLG EQ 'T' OR TRAN_FLG EQ 'F'

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN

 


WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel
 
Posts: 61 | Registered: March 12, 2008Report This Post
Virtuoso
posted Hide Post
quote:

EX -LINES * EDAPUT MASTER,ofc,CV,MEM
...
EDAPUT*

Alan,
This is new to me.
I usually put the number of lines instead of the *
Is EDAPUT* a sort of end-of-file?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Hi Danny,

Yes, EDAPUT* allows people like me who can't count an easy way to use EDAPUT. It is an EOF declaration.


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
Virtuoso
posted Hide Post
It also can come in handy if you are generating the text using a Dialog Manager loop and do not know a priori the number of lines.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Expert
posted Hide Post
quote:
people like me who can't count

I can vouch for that Smiler mainly because I have the same problem Wink

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Gold member
posted Hide Post
Hi all,
My mistake of join failure was:
I should not use a long field: A45 as index key. After I changed the index key in both main FOCUS table and temp FOCUS table to an A3 field, the JOIN works. Apparently the FOCUS index key has length limitation. Thanks. updated -02/18/2015.


quote:
Originally posted by Emily Lee:
Thanks for all your helpful suggestions. I searched the EDAPUT, then I found Francis Mariani’s WRITE method in the following post. It is easy for me to understand, so I took that approach..

http://forums.informationbuild...091012803#8091012803

I made it worked. I can do TABLE print of the FOCUS hold file which has all my input lines. But I got stocked with new error, which took me two days, still don’t know why….. I try to use the new FOCUS HOLD file to join with the main focus file, it seems the office name never matched in the JOIN. I have carefully checked the office name spelling in my default values, with the office name in my main focus table. What could be wrong??? Thanks in advanced.

Here is my code ( simplified) …
 

-**  I have 4 set of input parameters, here I simply it with 2 sets

-DEFAULT &FROM_OFC1 = '03-BALTIMORE';
-DEFAULT &TO_OFC1       = '12-NHC BALTIMORE';
-DEFAULT &TRAN_NUM_1 = '10';

-DEFAULT &FROM_OFC2 = '03-BALTIMORE';
-DEFAULT &TO_OFC2      = '12-NHC CHICAGO';
-DEFAULT &TRAN_NUM_2 = '20';

-**** my main FOC table is in my working folder, so I place my temp foc in the same folder, it may not be necessary, but I try to play safe…..

APP HOLD  my working folder
-RUN

-**********************  use FILEDEF and WRITE ***************
-** Create master for TRANSFILE  *****

FILEDEF DATAMAST DISK TRANSFILE.MAS
-RUN

-WRITE DATAMAST FILE=TRANSFILE, SUFFIX=FIX, $
-WRITE DATAMAST SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE DATAMAST FIELD=TRAN_FLG, ALIAS=TRAN_FLG, USAGE=A1, ACTUAL=A1, $
-WRITE DATAMAST FIELD=TRAN_PEND, ALIAS=TRAN_PEND, USAGE=A4, ACTUAL=A4, $
-WRITE DATAMAST FIELD=OFFICE, ALIAS=OFFICE, USAGE=A45, ACTUAL=A45, $

-** Create data file for TRANSFILE  *****
-** I have trouble use WRITE to enter Integer,  so I define ‘TRAN_PEND’ as Alpha, then will convert it to INT.
-** the OFFICE name is various length, so I place it to the last.


FILEDEF TRANSFILE DISK TRANSFILE.FTM
-RUN
-WRITE TRANSFILE F  &TRAN_NUM_1   &FROM_OFC1
-WRITE TRANSFILE T  &TRAN_NUM_1   &TO_OFC1
-WRITE TRANSFILE F  &TRAN_NUM_2   &FROM_OFC2
-WRITE TRANSFILE T  &TRAN_NUM_2   &TO_OFC2
-RUN


DEFINE FILE TRANSFILE
CNT/I11 = EDIT(TRAN_PEND);
TRAN_CNT/I11 = IF TRAN_FLG EQ 'T' THEN CNT
          ELSE IF TRAN_FLG  EQ 'F' THEN 0 - CNT
		  ELSE 0;
END

-* before using  SUM command,  I found the hold the file need to have the INDEX 
-*
TABLE FILE TRANSFILE
PRINT
    TRAN_CNT
    TRAN_FLG
BY OFFICE
ON TABLE HOLD AS TRANSTEMP FORMAT FOCUS INDEX OFFICE
END
-RUN

-** Sum the input file with office to reduce multiple office rows
TABLE FILE TRANSTEMP
SUM
    TRAN_CNT
    TRAN_FLG
BY OFFICE
ON TABLE HOLD AS TRANSHOLD FORMAT FOCUS INDEX OFFICE
END
-RUN
*

-*************************************************************
-*JOIN TO EM_MAIN TABLE, both table should have indexed with OFFICE
-**********************************************************
JOIN CLEAR *
JOIN OFFICE   IN   EM_MAIN  TO OFFICE  IN TRANSHOLD AS J1
-RUN

DEFINE FILE EM_MAIN
NEW_TOTAL_PEND/I11 =  PENDING +  TRAN_CNT;
END

-*****Try to print office which has match rows,  that is also has TRAN_FLG EQ ‘T’ or ‘F’

TABLE FILE EM_MAIN
PRINT
       TRANS_FLG
       EM_MAIN.SEG01.OFFICE
       EM_MAIN.NEW_TOTAL_PEND 
 BY  EM_ MAIN.OFFICE
  WHERE TRAN_FLG EQ 'T' OR TRAN_FLG EQ 'F'

ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
$
ENDSTYLE
END
-RUN

 


WebFOCUS 8.2.01 AppStudio
HTML, PDF, Excel
 
Posts: 61 | Registered: March 12, 2008Report 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>How to write data to a temp FOCUS table?

Copyright © 1996-2020 Information Builders