Hi All,
I am using a HOLD file (and reading from it using READ ) which is inside a loop. Every time loop comes back to starting point it inserts new data, but it seems HOLD file is not flushing the old data hence every time i get the old data.
I am using Oracle 8i on WinXP with WF713.
Thanks,
Shankar

Hi,
Please let me know if there is any syntax which i can use.
I tried finding some previous discussions but to no avalil.

are you using a -READ with NOCLOSE

No, i am not using NOCLOSE with READ.
Simple READ like this:
-READ TEMP_CNT &CNT_IP.A5
-RUN

When the loop comes back here it doesnt overwrite previous value, instead everytime it reads the first value stored here.

The -RUN is effectively closing the file TEMP_CNT. If you have your -READ in a loop, remove the -RUN, it is unnecessary.

You most likely do not have a -RUN after the TABLE FILE... PRINT... ON TABLE HOLD... END statement. You need the -RUN before the -READ.

Hi Alan, Francis,
I've removed all the -RUN after -READ inside the loop.
I do have a -RUN after the TABLE FILE... PRINT... ON TABLE HOLD... END statement.
Still i am getting the same result i.e. the variable populated by -READ is always showing the same old value. However when I do a view source of the report, i am getting below warning:
(FOC441) WARNING. THE FILE EXISTS ALREADY. CREATE WILL WRITE OVER IT
It gives me warning but never overwrites it.
Is there any direct way to flush the -READ variable and the HOLD file through which -READ is reading value.
Please help!!!

Hi Shankar,

Can you please post your complete code for us to take a look at? READ does not place data in ANY file.

Thank you in advance for sharing with all.

Cheers,

Kerry

A working example:

-*-SET &ECHO=ALL;

-SET &C1 = 'ENGLAND';
-SET &C2 = 'FRANCE';
-SET &C3 = 'CANADA';
-SET &C4 = 'ITALY';

-REPEAT ENDREP1 FOR &COUNTER FROM 1 TO 4
TABLE FILE CAR
SUM CNT.CAR CAR
WHERE COUNTRY EQ '&C.&COUNTER'
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

-READ H001 &CNT_CAR.I5. &CAR.A16.
-TYPE *******************************
-TYPE COUNTRY IS &C.&COUNTER / CAR COUNT IS &CNT_CAR / SAMPLE CAR IS &CAR
-TYPE *******************************

-ENDREP1

Are any of the TABLE files resulting in zero records? That is most likely the issue. As illustrated in the example above: no cars for Canada, but the car for France is being read.

This solves the issue:

-*-SET &ECHO=ALL;

-SET &C1 = 'ENGLAND';
-SET &C2 = 'FRANCE';
-SET &C3 = 'CANADA';
-SET &C4 = 'ITALY';

-REPEAT ENDREP1 FOR &COUNTER FROM 1 TO 4

-REP1
TABLE FILE CAR
SUM CNT.CAR CAR
WHERE COUNTRY EQ '&C.&COUNTER'
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN
-IF &LINES EQ 0 GOTO NODATA;

-READ H001 &CNT_CAR.I5. &CAR.A16.
-TYPE *******************************
-TYPE COUNTRY IS &C.&COUNTER / CAR COUNT IS &CNT_CAR / SAMPLE CAR IS &CAR
-TYPE *******************************
-GOTO ENDREP0

-NODATA
-SET &CNT_CAR = 0;
-SET &CAR = '';
-TYPE *******************************
-TYPE COUNTRY IS &C.&COUNTER / CAR COUNT IS &CNT_CAR / SAMPLE CAR IS &CAR
-TYPE *******************************

-ENDREP0

-ENDREP1

Hi,
Here is my code:
-SET &STARTSVCOFF = 2;
-SET &ENDSVCOFF = 4;
-LOOPBEGSVC
-SET &SVC_TEMP = '''' || SUBSTR(&SERVICE.LENGTH, &SERVICE, &STARTSVCOFF, &ENDSVCOFF, 3, 'A3') || '''';
-TYPE SVC_TEMP = &SVC_TEMP
SQL SQLORA
INSERT INTO APL_CCAM9.TEMP_CCAM_BASE_ALLOC_DETAILS
(YEAR_NBR)
SELECT BALLOC.YEAR_NBR
FROM APL_CCAM.CCAM_BASE_ALLOCATION BALLOC
WHERE 1=1
&YEAR_COND
AND BALLOC.SERVICE_CD = &SVC_TEMP
;
END
-RUN
-SET &CNT_IP = '';

USE CLEAR
JOIN CLEAR *
SQL SQLORA
SELECT YEAR_NBR CNT
FROM APL_CCAM9.TEMP_CCAM_BASE_ALLOC_DETAILS
WHERE ROWNUM = 1
;
TABLE
FILE SQLOUT
PRINT *
ON TABLE HOLD AS INP1 FORMAT FOCUS
END
-RUN
-TYPE DATA IS STORED IN WEBFOCUS HOLD FILE

JOIN CLEAR *
TABLE FILE INP1
SUM CNT NOPRINT
ON TABLE HOLD AS TEMP_CNT FORMAT ALPHA
END
-RUN
-READ TEMP_CNT &CNT_IP.A5

-TYPE DATA IS STORED IN WEBFOCUS VARIABLES
-TYPE CNT_IP = &CNT_IP


-IF &CNT_IP EQ '' THEN GOTO NOMATCHFND ELSE GOTO MATCHFND;
-NOMATCHFND
-TYPE INSIDE NOMATCHFND


-SET &STARTSVCOFF = &STARTSVCOFF + 6;
-SET &ENDSVCOFF = &ENDSVCOFF + 6;

-IF &SERVICE.LENGTH LT &STARTSVCOFF THEN GOTO 'LOOPENDSVC' ELSE GOTO 'LOOPBEGSVC';

-LOOPENDSVC

Hope this helps you ppl to help me out...

WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML

TABLE FILE INP1
SUM CNT NOPRINT
ON TABLE HOLD AS TEMP_CNT FORMAT ALPHA
END

Is it possible to have zero records resulting from this TABLE FILE? If so, use the technique I showed you in my last posting to this thread.