Hi,

I have procedure with 10 systems and 3 regions, each system creates one hold file. These 10 hold files are combined by using USE command. Now repeating the same procedure by passing region value to separate the report region wise. First hold file creating correctly and when creating for region2 hold file it is appending the region1 records also. When I pass region3 the file comes with all three regions data.
I used USE CLEAR * but not clearing. I want clear for each loop and it should be like first time run

Example:

-SET &ECHO = 'ALL';
-SET &RGN = 'A1';
-SET &FINLFILE = 'FILEA';
-GOTO STRTPRG;
-B1
-SET &RGN = 'B1';
-SET &FINLFILE = 'FILEB';
-GOTO STRTPRG;
-C1
-SET &RGN = 'C1';
-SET &FINLFILE = 'FILEC';
-STRTPRG
-IF &RGN NE 'A1' THEN GOTO SKIPA1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HOLD1 FORMAT FOCUS
END
-RUN
-*-*
-SKIPA1
-IF &RGN NE 'B1' THEN GOTO SKIPB1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS HOLD2 FORMAT FOCUS
END
-RUN
-SKIPB1
-*
-IF &RGN NE 'C1' THEN GOTO SKIPC1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'ITALY';
ON TABLE HOLD AS HOLD3 FORMAT FOCUS
END
-RUN
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'FRANCE';
ON TABLE HOLD AS HOLD4 FORMAT FOCUS
END
-RUN
-SKIPC1
USE CLEAR *
USE
HOLD1 AS HOLD1
HOLD2 AS HOLD1
HOLD3 AS HOLD1
HOLD4 AS HOLD1
END
-RUN
APP HOLD acctpay
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS &FINLFILE FORMAT FOCUS
END
-RUN
-IF &FINLFILE EQ 'FILEA' THEN GOTO B1 ELSE IF &FINLFILE EQ 'FILEB' THEN GOTO C1 ELSE GOTO EXTPRG;
-EXTPRG
-EXIT

Thanks,Koti
WF7703, Windows 7

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

This sample fex does not run all the way through - I get an error because HOLD2-HOLD4 do not exist.

The problem might be in the APP HOLD command. The FOCUS databases are physically created in app folder acctpay, so they will be there if you loop through the fex several times. Though it might also be possible that they'll repeatedly be read even if they're not in the app folder. Perhaps you need to append the region to the database name when creating/reading the databases.

To use your code as a base, you just need a little judicial use of Dialog Manager magic -

-SET &ECHO = 'ALL';
-DEFAULTH &Hold1 = '', &Hold2 = '', &Hold3 = '', &Hold4 = ''
-SET &RGN = 'A1';
-SET &FINLFILE = 'FILEA';
-GOTO STRTPRG;
-B1
-SET &RGN = 'B1';
-SET &FINLFILE = 'FILEB';
-GOTO STRTPRG;
-C1
-SET &RGN = 'C1';
-SET &FINLFILE = 'FILEC';
-STRTPRG
-IF &RGN NE 'A1' THEN GOTO SKIPA1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'ENGLAND';
ON TABLE HOLD AS HOLD1 FORMAT FOCUS
END
-RUN
-SET &Hold1 = IF &LINES EQ 0 THEN '' ELSE 'HOLD1 AS HOLD1';
-*
-SKIPA1
-*
-IF &RGN NE 'B1' THEN GOTO SKIPB1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'JAPAN';
ON TABLE HOLD AS HOLD2 FORMAT FOCUS
END
-RUN
-SET &Hold2 = IF &LINES EQ 0 THEN '' ELSE 'HOLD2 AS HOLD1';
-*
-SKIPB1
-*
-IF &RGN NE 'C1' THEN GOTO SKIPC1;
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'ITALY';
ON TABLE HOLD AS HOLD3 FORMAT FOCUS
END
-RUN
-SET &Hold3 = IF &LINES EQ 0 THEN '' ELSE 'HOLD3 AS HOLD1';
-*
TABLE FILE CAR
PRINT CAR COUNTRY RETAIL_COST
WHERE COUNTRY EQ 'FRANCE';
ON TABLE HOLD AS HOLD4 FORMAT FOCUS
END
-RUN
-SET &Hold4 = IF &LINES EQ 0 THEN '' ELSE 'HOLD4 AS HOLD1';
-*
-SKIPC1
USE CLEAR *
USE
&Hold1.EVAL
&Hold2.EVAL
&Hold3.EVAL
&Hold4.EVAL
END
-RUN
-*APP HOLD acctpay
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS &FINLFILE FORMAT FOCUS
END
-RUN
-IF &FINLFILE EQ 'FILEA' THEN GOTO B1 ELSE IF &FINLFILE EQ 'FILEB' THEN GOTO C1 ELSE GOTO EXTPRG;
-EXTPRG
-EXIT

T

Hi Tony,

Thanks for the information. I have implemented logic by using Dialog manager commands.

Thanks,
Koti

Koti,

You're welcome.

Incidentally, I would be inclined to use the following syntax at the end of your sample code as it would ensure that the only items written to your target application folder are the .MAS and .FOC files that you want.

-*APP HOLD acctpay
-RUN
TABLE FILE HOLD1
PRINT *
ON TABLE HOLD AS acctpay/&FINLFILE FORMAT FOCUS
END
-RUN
-IF &FINLFILE EQ 'FILEA' THEN GOTO B1 ELSE IF &FINLFILE EQ 'FILEB' THEN GOTO C1 ELSE GOTO EXTPRG;
-EXTPRG
-EXIT

T