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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Adding row to a hold table
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Adding row to a hold table
 Login/Join
 
Member
posted
Can anyone tell me if there is a way to add rows to a hold table. In this case I need to run the same procedure x number of times and have it right one row each time but retain the rows until the loop is completed when I can print them

This message has been edited. Last edited by: Kerry,


DEV: WebFOCUS 7.7.02 OS: DB2, Universe
OUTPUT : Html, Excel, PDF, Html(active)
 
Posts: 13 | Location: Kentucky, USA | Registered: August 16, 2007Reply With QuoteReport This Post
Virtuoso
posted Hide Post
FILEDEF ddname DISK holdfilename (APPEND
should do the trick...


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
my code looks like this...

FILEDEF DLY_AVGS DISK D_A (APPEND

TABLE FILE DLY_AVGS
PRINT
E_YESTERDAY
E_DAY
DLY_AVG
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS D_A FORMAT FOCUS


However when I print the D_A hold table I still just get the last row added...


DEV: WebFOCUS 7.7.02 OS: DB2, Universe
OUTPUT : Html, Excel, PDF, Html(active)
 
Posts: 13 | Location: Kentucky, USA | Registered: August 16, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
You cannot do what GamP suggested with a FOCUS DB. Either change your HOLD to a ALPHA HOLD file and then use GamP's suggestion, or use something like for a FOCUS DB HOLD file:

TABLE FILE CAR
SUM SALES
BY COUNTRY
ON TABLE HOLD AS HCAR FORMAT FOCUS INDEX COUNTRY
END
-RUN

MODIFY FILE HCAR
FREEFORM COUNTRY/10 SALES/6

MATCH COUNTRY
  ON MATCH REJECT
  ON NOMATCH INCLUDE

DATA
COUNTRY='CANADA',
SALES=044602, $
END
-RUN

TABLE FILE HCAR
PRINT *
END

This message has been edited. Last edited by: Francis Mariani,


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, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Hi,

In addition to Francis' suggestion, you can also do something like that:

TABLE FILE CAR
SUM
SALES
BY COUNTRY
ON TABLE HOLD AS XHOLD1
END
-RUN
TABLE FILE CAR
PRINT
COMPUTE COUNTRY/THEFORMAT='BLAH';
COMPUTE SALES/THEFORMAT=123456;
WHERE RECORDLIMIT EQ 1
ON TABLE HOLD AS XHOLD2
END
-RUN
TABLE FILE XHOLD1
PRINT
SALES COUNTRY
MORE
FILE XHOLD2
END

Regards,

This message has been edited. Last edited by: ferran,


WF 7.7.03,Win XP Pro.
HTML, PDF, EXCEL, AHTML, FLEX, APDF.
SQL Server, MySQL, DB2, FOCUS.
 
Posts: 19 | Registered: February 04, 2011Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the responses. Maybe it will help if I explain my full dilemma.

I have a FEX that computes a complex average. It works on a date and I need it to run for 28 different dates.

-SET &days = 28;
-REPEAT AVG_FOR_DAY WHILE &days GT 0;

-INCLUDE AVERAGING_FEX

-SET &days = &days - 1;
-AVG_FOR_DAY


So it runs 28 times and writes over the FOCUS HOLDFILE 28 times so when the loop is done I only have my last date average. We are ORACLE and I don't believe the FILEDEF will work for me. I need to somehow be able to retain the 28 values in a table.

If I just have it pass me back the values as parameters how do I write them 28 times into some kind of table?

I appreciate any help or different approaches.


DEV: WebFOCUS 7.7.02 OS: DB2, Universe
OUTPUT : Html, Excel, PDF, Html(active)
 
Posts: 13 | Location: Kentucky, USA | Registered: August 16, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
I'm sure that this same question was asked in the forum only in the last 6 months or so.


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: 6309 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
There are several ways of taking care of this, here's one:

Before the -REPEAT, allocate the HOLDFILE with the append tag:
FILEDEF HOLDFILE DISK HOLDFILE.FTM (APPEND

This will append the 28 files together.

-SET &ECHO=ALL;

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-SET &CCOUNT = &LINES;

FILEDEF H002 DISK H002.FTM (APPEND
-RUN

-REPEAT ENDREP1 &CCOUNT TIMES

-*== AVERAGING_FEX START =====
-READ H001 NOCLOSE &COUNTRY.A10. 

TABLE FILE CAR
SUM
SALES BY COUNTRY
WHERE COUNTRY EQ '&COUNTRY'
ON TABLE HOLD AS H002 FORMAT ALPHA
END
-RUN
-*== AVERAGING_FEX END =====

-ENDREP1

TABLE FILE H002
PRINT *
END
-RUN


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, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Your AVERAGING_FEX could look something like this:
FILEDEF D_A DISK D_A.FTM (APPEND

TABLE FILE DLY_AVGS
PRINT
E_YESTERDAY
E_DAY
DLY_AVG
ON TABLE HOLD AS D_A FORMAT ALPHA
END

At the end of your loop you will now have a fixed record alpha file, containing all 28 records. You can do with this file whatever you wish, you can also TABLE it and hold it as focus format if you really need that.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1960 | Location: Netherlands | Registered: September 25, 2007Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for everyone's input. It looks like the best way for me to go was with the FILEDEF with (APPEND with an APLHA format HOLD file, which I can then re-format into a FOCUS HOLD table.

Thanks again.


DEV: WebFOCUS 7.7.02 OS: DB2, Universe
OUTPUT : Html, Excel, PDF, Html(active)
 
Posts: 13 | Location: Kentucky, USA | Registered: August 16, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Francis,

How would i write this modify syntax for a Hold FIle with Multiple Sort Phrases, the intent is to add a line of data to make the graphs consistent.

The HOLD looks like:
TABLE FILE BWHOLD_CCA_CHART
PRINT
VOLUME
FIXED_COST__EXCL__DEP_
PLANT_SHORT
PLANT_CURR
PLANT_COUNTRY
HIER
STRUCTURE
COSTCENTER__0PROFIT_CTR_MEDIUM_NAME
BW_STATISTICAL_KEY_FIGURES_LEVEL_01
BW_STATISTICAL_KEY_FIGURES_LEVEL_02/
BY COST_STRUCTURE_LEVEL_00
BY FISCYEAR_NAME
BY MONTH_CSV
BY DATE_CSV
BY FISCPER3_NAME
BY PERIOD
BY CURRENCY_NAME
BY CURTYPE_NAME
BY COSTCENTER__0PROFIT_CTR_MEDIUM_NAME
BY ALL_NAW_LEVEL_01
BY ALL_NAW_LEVEL_02
BY ALL_NAW_LEVEL_03
BY ALL_NAW_LEVEL_04
BY ALL_NAW_LEVEL_05
END

Thanks,
Kumar


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 64 | Location: Toronto, Ontario | Registered: May 15, 2014Reply With QuoteReport This Post
Master
posted Hide Post
It's possible to SQL the HOLD files:

TABLE FILE CAR
	SUM SALES
	BY	CAR
ON TABLE HOLD AS HOLD_CAR
END

SQL
insert into HOLD_CAR ( CAR , SALES ) VALUES ( 'MCLAREN' , 200000 );
END
-RUN

TABLE FILE HOLD_CAR
	PRINT *
END


Much easier in most cases I think.


_____________________
WF: 8.0.0.9 > going 8.2.0.5
 
Posts: 669 | Location: Veghel, The Netherlands | Registered: February 16, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Dave
Good One


WF Production :- WF:8.0.0.4, 8.1.05 App-studio/Developer Studio(8.1.x) ,
8.2.0.1M , 8.2.0.2 (App-Studio8.2.x),
InfoAssist/+, InfoDiscovery
Output format:-AHTML, PDF, Excel, HTML
Platform:-Windows 7, 8,10
 
Posts: 186 | Location: Infobuild India | Registered: August 28, 2015Reply With QuoteReport This Post
Expert
posted Hide Post
Dave! I never tried using SQL to INSERT into a HOLD file! Great idea.


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, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
I never tried using SQL to INSERT into a HOLD file! Great idea.



Ditto to that


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: 6309 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
The WebFOCUS SQL Translator allows you to issue SQL statements, and then WebFOCUS generates (translates it into) the FOCUS code.

Adding the 'FILE' command allows you to capture the generated code.

For example...:
TABLE FILE CAR
	SUM SALES
	BY  CAR
ON TABLE HOLD AS HOLD_CAR
END
-*
SQL
insert into HOLD_CAR ( CAR , SALES ) VALUES ( 'MCLAREN' , 200000 );
FILE MYFEXNAME
END
-RUN
-*
TYPE MYFEXNAME.FEX
-EXIT  

...yields the generated MODIFY code:
0 NUMBER OF RECORDS IN TABLE=       18  LINES=     10
 (FOC1291) RECORDS AFFECTED DURING CURRENT REQUEST  : 1/INSERT
 0 TRANSACTIONS:         TOTAL =     1  ACCEPTED=     1  REJECTED=     0
 SEGMENTS:             INPUT =     1  UPDATED =     0  DELETED =     0
 SET COUNTWIDTH=ON
 -SET SQLERRNUM = 0;
 MODIFY FILE HOLD_CAR
 FREEFORM CAR
 FREEFORM SALES
 MATCH CAR
 MATCH SALES
 ON NOMATCH INCLUDE
 ON MATCH REJECT
 DATA
 'MCLAREN',200000,$
 END
 -RUN
 -IF &SQLERRNUM GT 0 GOTO SQLEXT01;
 -SET SQLERRNUM = &FOCERRNUM;
 -IF &RETCODE   GT 1 GOTO SQLEXT01;
 -IF &RETCODE   LT 0 GOTO SQLEXT01;
 -IF &FOCERRNUM GT 0 GOTO SQLEXT01;
 -SQLEXT01
 -IF &SQLERRNUM GT 0 GOTO SQLFIN;
 -SQLFIN
 POSTSTAT FOCERRNUM &SQLERRNUM
 -* End of SQLTRANS Generated FOCEXEC
  

One of my FOCUS mentors told me they thought that one of the reasons the SQL Translator was created was to assist folks strong in SQL to learn the FOCUS language.

While I haven't had the need to use SQL Translator, it is an interesting tool to have.

:-)
 
Posts: 815 | Registered: April 23, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
-SQLFIN
POSTSTAT FOCERRNUM &SQLERRNUM
-* End of SQLTRANS Generated FOCEXEC


Anybody encountered "POSTSTAT" command before?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Adding row to a hold table

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