I created a report that uses an sql statement along with some computes, defines and variables and seems to be working fine. Since the initial sql statement and defines can be used for other reports, I thought about putting it in its own fex so I could refer to it without having multiple copies of it in each new report created (so that if changes need to be made, they would only need to change in one spot). I modified the existing report to use a -Include in the section where the sql and PRINT statements were. It works going through the first loop, but when it runs through the second loop, it is not affecting the WHERE statement. At first I thought that the WHERE statement might not be working because perhaps the ampervariable was being reset to the default value, but I'm not sure this is the case because I use the ampervariable to change the header description and the header is displaying correctly. Maybe I don't have the WHERE in the right spot? I created an example using car, but that version is working fine it loops and changes the where clause correctly), so maybe is an issue with using sql to create the FOCUS format HOLD file?

DEFINES.fex

-DEFAULT &TERM = '201230_45'

SET ASNAMES = ON
SET SHOWBLANKS = ON
SET HOLDLIST = PRINTONLY

-SET &TERMSTART =  SUBSTR(9, &TERM, 1, 6, 6, 'A9');
-SET &TERMEND = IF POSIT(&TERM, 9, '_', 1, 'I6') EQ 0 THEN &TERMSTART ELSE SUBSTR(9, &TERM, 1, 4, 4, 'A9') || SUBSTR(9, &TERM,8,9,2,'A9');
-SET &TERMWHERE = IF &TERMEND.LENGTH LT 3 THEN ' = ' || &TERMSTART  ELSE ' in (' || &TERMSTART || ', ' || &TERMEND || ')';
-SET &TIME = EDIT(&TOD,'99:$99:$99');

ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
       select pidm, term AS academic_period, student_level, stu_population,
			   CASE WHEN college in ('AM','SC') THEN 'SC'
			        WHEN college in ('BN','BU') THEN 'BU'
					WHEN college in ('PL','PS') THEN 'PL'
					ELSE college
			   END AS college,
			   residency,
               APPLIED,
               ACCEPTED,
               Deposit,
			   CASE WHEN Accepted = 1 AND No_Deposit = 1 and Cancelled = 0 THEN 1 ELSE 0 END AS No_Deposit,
			   Cancelled,
               Deferred,
               Denied,
               Pending,
               Withdrawn,
               Waitlist,
               Hold
       from usf_stu.usf_as_status_slot a
       where SUBSTR(PROGRAM,1,2) <> 'ND'
	   AND college NOT IN ('00', 'LW')
       AND stu_population   not in ('J', 'S', 'V')
	   AND term between &TERMSTART and &TERMEND
;
END
TABLE FILE SQLOUT
PRINT
     COMPUTE TERMYR/A9 = '&TERM';
     STUDENT_LEVEL/A2
     COMPUTE STU_LEVEL/A2 = IF STUDENT_LEVEL EQ 'GR' OR STUDENT_LEVEL EQ 'DR' THEN 'GR' ELSE STUDENT_LEVEL;
     STU_POPULATION/A2
     COMPUTE STU_POP/A16 = IF STU_POPULATION EQ 'G' OR STU_POPULATION EQ 'D' OR STU_POPULATION EQ 'Y' OR STU_POPULATION EQ 'Z' THEN 'G' ELSE STU_POPULATION;
     COMPUTE STU_POP_DESC/A16 = DECODE STU_POP( F FRESH T TRANS P '2+2' R RETURNS G GRAD ELSE STU_POP );
     RESIDENCY/A2
     COLLEGE/A2
     APPLIED/I5
     ACCEPTED/I5
     DEPOSIT/I5
     NO_DEPOSIT/I5
     CANCELLED/I5
     DEFERRED/I5
     DENIED/I5
     PENDING/I5
     WITHDRAWN/I5
     WAITLIST/I5
     COMPUTE HOLDS/I5 = HOLD;
-*This next line is now in the main fex
-*WHERE RESIDENCY EQ '&RESIDENCY.(<All,_FOC_NULL>,<Domestic,D>,<International,I>).RESIDENCY.';
ON TABLE NOTOTAL
ON TABLE HOLD AS STATSLOTDETAIL FORMAT FOCUS
END
  

Main File

SET NOCLOSE = ON
SET ASNAMES = ON
SET SHOWBLANKS = ON
-SET &OC = 'OPEN';
-DEFAULT &RESIDENCY = 'FOC_NONE'

-REPEAT MBA120LOOP 2 TIMES
-INCLUDE Defines
-SET &HDR = 'Term ' | &TERMSTART | 'through ' | &TERMEND;
-SET &HDR1 = IF &RESIDENCY EQ 'I' THEN 'International' ELSE '';
-RUN
DEFINE FILE STATSLOTDETAIL ADD
STU_POP/A16=STU_POP;
STU_POP1/A16='ZTOTAL';
STU_POP2/A16=STU_POP;
STU_POP_DESC1/A16=STU_POP1;
STU_POP_DESC2/A16=STU_POP_DESC;
END
TABLE FILE STATSLOTDETAIL
SUM
     APPLIED
     ACCEPTED
     DEP
     NO_DEPOSIT
     CANCELLED
     DEFERRED
     DENIED
     PENDING
     WITHDRAWN
     WAITLIST
     HOLDS
BY  LOWEST TERMYR
BY  HIGHEST STU_LEVEL
BY  LOWEST STU_POP1
BY  STU_POP_DESC1
BY  LOWEST COLLEGE

WHERE RESIDENCY EQ '&RESIDENCY.(<All,_FOC_NULL>,<Domestic,D>,<International,I>).RESIDENCY.';
ON TABLE NOTOTAL
ON TABLE HOLD AS STATSLOTTOTAL
END
-RUN

DEFINE FILE STATSLOTTOTAL ADD
STU_POP/A16=STU_POP1;
STU_POP2/A16=IF STU_POP IS 'ZTOTAL' THEN ' '|STU_LEVEL ELSE STU_POP;
STU_POP_DESC/A16=STU_POP_DESC1;
STU_POP_DESC2/A16=IF STU_POP IS 'ZTOTAL' THEN ' '|STU_LEVEL ELSE STU_POP_DESC;
END
TABLE FILE STATSLOTTOTAL
SUM
     APPLIED/I5 AS 'Applied'
     ACCEPTED/I5 AS 'Accepted'
     DEP/I5 AS 'Deposit'
     NO_DEPOSIT/I5 AS 'No Deposit'
     CANCELLED/I5 AS 'Cancelled'
     DEFERRED/I5 AS 'Deferred'
     DENIED/I5 AS 'Denied'
     PENDING/I5 AS 'Pending'
     WITHDRAWN/I5 AS 'Withdrawn'
     WAITLIST/I5 AS 'Waitlist'
     HOLDS/I5 AS 'Hold'
BY  LOWEST TERMYR NOPRINT
BY  HIGHEST STU_LEVEL AS 'Level'
BY  LOWEST STU_POP NOPRINT
BY  LOWEST STU_POP2 AS Stu_Pop
BY  LOWEST STU_POP_DESC2 NOPRINT
BY  LOWEST COLLEGE AS 'College'

ON STU_POP_DESC2 SUBTOTAL AS '*TOTAL STU_POP' NOSPLIT
HEADING
"DATE RUN  <+0>&DATE <+0>                                                                                         This is Header Line 1                                                                                    REPORT PAGE <TABPAGENO"
"TIME RUN  <+0>&TIME <+0>                                                                                   This is Header Line 2 Information                                                                                  PROGRAM ID MBA 120"
"                                                                                                                      &HDR"
"     &HDR1"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE PCHOLD FORMAT PDF &OC
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA,
     FONT='ARIAL',
$
TYPE=TITLE,
	 FONT='ARIAL',
$
TYPE=HEADING,
     LINE=1,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=2,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=3,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=2,
     OBJECT=TEXT,
     ITEM=4,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=3,
     JUSTIFY=LEFT,
$
TYPE=HEADING,
     LINE=3,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
TYPE=HEADING,
     LINE=4,
     OBJECT=TEXT,
     ITEM=1,
     SIZE=9,
     STYLE=NORMAL,
$
     PAGESIZE='Letter',
     ORIENTATION=LANDSCAPE,
$
ENDSTYLE
MORE
FILE STATSLOTDETAIL
END
-RUN
-SET &OC='CLOSE';
-SET &RESIDENCY = 'I';
-MBA120LOOP
-ENDREPEAT

  

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

Would you please show this scenario in a simplified fex using an IB Sample table to make it easy on us? This way we can concentrate on the issue instead of learning your application.

Hi Doug,

I planned to provide the example using car, but at that point, I thought it was working, so that is why didn't post that one. Now it seems to not be working, so here it is. Hope that with this example it will be easier to figure out the issue.

First is the original version (Self Contained), then the version using include (DefineCar.fex) and Main Report.

NOTE: ION is amperREGION
ION_HDR is amperREGION_HDR

Original Version

 -DEFAULT ®ION = 'FOC_NONE'
-SET &OC='OPEN NOBREAK';
-SET &OPR='NE';
-REPEAT CARLOOP 2 TIMES
-SET ®ION_HDR = IF ®ION EQ '_FOC_NULL' THEN 'All Regions' ELSE ®ION;
DEFINE FILE CAR
REGION/A20=IF COUNTRY EQ 'ENGLAND' OR COUNTRY EQ 'JAPAN' THEN 'ISLAND' ELSE 'MAINLAND';
END
TABLE FILE CAR
SUM
     COMPUTE CARSOLD/I3 = SALES / DEALER_COST;
BY  LOWEST REGION NOPRINT
BY  LOWEST COUNTRY
BY  LOWEST CAR

ON COUNTRY SUBTOTAL AS '*TOTAL'
HEADING
"®ION_HDR"
WHERE REGION EQ '®ION.(<ALL,_FOC_NULL>,<MAINLAND,MAINLAND>,<ISLAND,ISLAND>).REGION.';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML &OC
-*ON TABLE PCHOLD FORMAT PDF &OC
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$


ENDSTYLE
END
-RUN
-SET &OC='CLOSE';
-SET &OPR='EQ';
-SET ®ION = 'MAINLAND';
-CARLOOP
-ENDREPEAT
 

Definecar.fex

 DEFINE FILE CAR
REGION/A20=IF COUNTRY EQ 'ENGLAND' OR COUNTRY EQ 'JAPAN' THEN 'ISLAND' ELSE 'MAINLAND';
END
TABLE FILE CAR
PRINT
    REGION
	COUNTRY
	CAR
    COMPUTE CARSOLD/I3 = SALES / DEALER_COST;

ON TABLE NOTOTAL
ON TABLE HOLD AS CARDEFS FORMAT FOCUS
END
 

Main Report

 SET NOCLOSE = ON
-*DEFAULTH ®ION = 'FOC_NONE'
-DEFAULT ®ION = 'FOC_NONE'
-SET &OC='OPEN NOBREAK';
-SET &OPR='NE';
-REPEAT CARLOOP 2 TIMES
-SET ®ION_HDR = IF ®ION EQ '_FOC_NULL' THEN 'All Regions' ELSE ®ION;
-INCLUDE CARDEFS

TABLE FILE CARDEFS
SUM
     CARSOLD
BY  LOWEST REGION NOPRINT
BY  LOWEST COUNTRY
BY  LOWEST CAR

ON COUNTRY SUBTOTAL AS '*TOTAL'
HEADING
"®ION_HDR"
WHERE REGION EQ '®ION.(<ALL,_FOC_NULL>,<MAINLAND,MAINLAND>,<ISLAND,ISLAND>).REGION.';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML &OC
-*ON TABLE PCHOLD FORMAT PDF &OC
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$

ENDSTYLE
END
-RUN
-SET &OC='CLOSE';
-SET &OPR='EQ';
-SET ®ION = 'MAINLAND';
-CARLOOP
-ENDREPEAT
 

The first thing I noticed was this line of code:

SET NOCLOSE = ON

Running the code as-is ends up with the following errors:

0 ERROR AT OR NEAR LINE      3  IN PROCEDURE FPSX_CARFOCEXEC *
(FOC324) THE PARAMETER TO BE SET IS NOT RECOGNIZED: NOCLOSE
0 NUMBER OF RECORDS IN TABLE=       18  LINES=     18
0 ERROR AT OR NEAR LINE     24  IN PROCEDURE FPSX_CARFOCEXEC *
(FOC096) WARNING. NO TEXT SUPPLIED BELOW SUBHEAD OR SUBFOOT
0 NUMBER OF RECORDS IN TABLE=       18  LINES=     10
0 HOLDING HTML FILE ON PC DISK ...
(FOC3298) ERROR FOUND IN A COMPOUND REPORT
Compound Report is TERMINATING.....

NOCLOSE is used in conjunction with a -READ and -WRITE statement - it's not a SET command.

Then there's the _FOC_NULL, should it be FOC_NONE instead? I'm not sure if this some kind of server setting where my server has FOC_NONE as default and yours has _FOC_NULL, but try changing that.

Revised code:

-SET &ECHO=ALL;

-*DEFAULTH ®ION = 'FOC_NONE'
-DEFAULT ®ION = 'FOC_NONE'
-SET &OC='OPEN NOBREAK';
-SET &OPR='NE';
-REPEAT CARLOOP 2 TIMES

-SET ®ION_HDR = IF ®ION EQ 'FOC_NONE' THEN 'All Regions' ELSE ®ION;

-INCLUDE FPSX_DEFINECAR

TABLE FILE CARDEFS
SUM
CARSOLD
BY  LOWEST REGION NOPRINT
BY  LOWEST COUNTRY
BY  LOWEST CAR

ON COUNTRY SUBTOTAL AS '*TOTAL'

ON TABLE COLUMN-TOTAL AS 'TOTAL'

WHERE REGION EQ '®ION.(<ALL,_FOC_NULL>,<MAINLAND,MAINLAND>,<ISLAND,ISLAND>).REGION.';

HEADING
"®ION_HDR"

ON TABLE SET PAGE NOLEAD

ON TABLE PCHOLD FORMAT HTML &OC
-*ON TABLE PCHOLD FORMAT PDF &OC
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
-RUN

-SET &OC='CLOSE';
-SET &OPR='EQ';
-SET ®ION = 'MAINLAND';
-CARLOOP
-ENDREPEAT

DEFINE FILE CAR
REGION/A20=IF COUNTRY EQ 'ENGLAND' OR COUNTRY EQ 'JAPAN' THEN 'ISLAND' ELSE 'MAINLAND';
END
-RUN

TABLE FILE CAR
PRINT
REGION
COUNTRY
CAR
COMPUTE CARSOLD/I3 = SALES / DEALER_COST;

ON TABLE NOTOTAL
ON TABLE HOLD AS CARDEFS FORMAT FOCUS
END
-RUN

Thank you for your reply. I had no problems with Error messages running the code I posted. The problem with the Car version is it is not totaling correctly. The self contained version gives Totals of 31 for "All Regions" and 3 for MainLand. The Include version, shows 40 and 11. When I tried your modified version, there is no change in the totals from the Include version I posted.

As for my actual report, it appears to give the correct totals for the "All" but then repeats the "All" totals for the "International" as if it is ignoring the where statement. No Errors prevent the report from displaying the results in the browser.

How about just clearing all your defines as you enter the loop and re-defining them again. Same for filedefs....

George, I wasn't sure about how to clear the defines and redefine.

I took a step back and tried to rebuild the procedure and I think I fixed the problem of my code (not the car version). In the define fex I changed the ON TABLE HOLD AS STATSLOTDETAIL to STATUSSLOTDATA, thinking that the next step is redoing and redefining the same table and maybe it is better to save it to another name than to save it back to itself. The other change I made was to put a PRINT statement and move the WHERE clause to that section (which is how it is in the original version before trying the INCLUDE) as I had taken out the PRINT statement thinking that since it was already printed in the include that would have handled it.

Thanks to all who assisted.

DEFINE FILE filename CLEAR removes all defines for filename.