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 themThis message has been edited. Last edited by: Kerry,
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
March 09, 2011, 01:45 PM
ferran
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,
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'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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
March 09, 2011, 04:05 PM
Francis Mariani
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
March 10, 2011, 08:18 AM
GamP
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
March 10, 2011, 08:36 AM
Joek
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.
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
January 16, 2018, 03:15 AM
Dave
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
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
January 16, 2018, 03:52 PM
Waz
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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
January 16, 2018, 04:19 PM
David Briars
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.
:-)
January 16, 2018, 05:18 PM
j.gross
quote:
-SQLFIN POSTSTAT FOCERRNUM &SQLERRNUM -* End of SQLTRANS Generated FOCEXEC