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,

FILEDEF ddname DISK holdfilename (APPEND
should do the trick...

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...

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,

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 appreciate any help or different approaches.

I'm sure that this same question was asked in the forum only in the last 6 months or so.

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

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.

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.

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

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.

Dave

Dave! I never tried using SQL to INSERT into a HOLD file! Great idea.

quote:

I never tried using SQL to INSERT into a HOLD file! Great idea.

Ditto to that

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.

:-)

quote:

-SQLFIN
POSTSTAT FOCERRNUM &SQLERRNUM
-* End of SQLTRANS Generated FOCEXEC

Anybody encountered "POSTSTAT" command before?