Hello all.

I have this block of code at the tail end of my program. I am attempting to list out the top 2 sequences of a student's grade change and print in a flat file. The LIST seems to work fine. However, in the very last section, the output has an extra line in the Excel spreadsheet with only some data that I don't really need. Also, the line that I do need, are missing previous grades, some anyway. Can anyone offer any suggestions? See code snippet below. P

-********code starts******************
TABLE FILE NEWFILE2
LIST ID
FIRST_NAME
LAST_NAME
STUDENT_LEVEL AS 'STULVL'
STANDING
HONORS
CGPA
TGPA
NEWFILE2.COURSE_REFERENCE_NUMBER
STUDENT_COURSE.COURSE_LEVEL
NEWFILE2.SUBJECT AS 'SUBJ'
NEWFILE2.COURSE_NUMBER AS 'CRSENUM'
NEWFILE2.ACADEMIC_PERIOD AS 'CRSETERM'
GRADE_CHANGE_DATE AS 'CDATE'
NEWFILE2.FINAL_GRADE
INC_GRADE
FINAL_GRADE_SEQUENCE_NUMBER AS 'SEQ'
GRADE_CHANGE_REASON_DESC
GRADE_COMMENT
GRADE_COMMENT_DESC
NEWFILE2.COLLEGE
NEWFILE2.DEPARTMENT_DESC
NEWFILE2.INSTRUCTOR
SPRT
GRDTERM
BY NEWFILE2.COURSE_REFERENCE_NUMBER
BY PERSON_UID
ON TABLE HOLD AS FLATTEN
END
-*****************flatten file**********************
DEFINE FILE FLATTEN
CID/A15 = IF FLATTEN.LIST EQ 1 THEN ID ELSE ' ';
FIRST/A300 = IF FLATTEN.LIST EQ 1 THEN FIRST_NAME ELSE ' ';
LASTN/A300 = IF FLATTEN.LIST EQ 1 THEN LAST_NAME ELSE ' ';
LVL/A15 = IF FLATTEN.LIST EQ 1 THEN STULVL ELSE ' ';
ASTD/A150 = IF FLATTEN.LIST EQ 1 THEN STANDING ELSE ' ';
HONORS/A10 = IF FLATTEN.LIST EQ 1 THEN HONORS ELSE ' ';
CGPA/D12.2 = IF FLATTEN.LIST EQ 1 THEN CGPA ELSE ' ';
TGPA/D12.2 = IF FLATTEN.LIST EQ 1 THEN TGPA ELSE ' ';
CRN/A150 = IF FLATTEN.LIST EQ 1 THEN COURSE_REFERENCE_NUMBER ELSE ' ';
COLVL/A15 = IF FLATTEN.LIST EQ 1 THEN COURSE_LEVEL ELSE ' ';
SUBJ/A150 = IF FLATTEN.LIST EQ 1 THEN SUBJ ELSE ' ';
CRSENUM/A5 = IF FLATTEN.LIST EQ 1 THEN CRSENUM ELSE ' ';
CRSETERM/I6 = IF FLATTEN.LIST EQ 1 THEN CRSETERM ELSE ' ';
CDATE/A10 = IF FLATTEN.LIST EQ 1 THEN CDATE ELSE ' ';
GRADE/A15 = IF FLATTEN.LIST EQ 1 THEN FINAL_GRADE ELSE ' ';
PREV/A15 = IF FLATTEN.LIST EQ 2 THEN FINAL_GRADE ELSE ' ';
I?/A3 = IF FLATTEN.LIST EQ 1 THEN INC_GRADE ELSE ' ';
REASON/A150 = IF FLATTEN.LIST EQ 1 THEN GRADE_COMMENT ELSE ' ';
COMMENT/A300 = IF FLATTEN.LIST EQ 1 THEN GRADE_COMMENT_DESC ELSE ' ';
COL/A150 = IF FLATTEN.LIST EQ 1 THEN COLLEGE ELSE ' ';
DEPT/A150 = IF FLATTEN.LIST EQ 1 THEN DEPARTMENT_DESC ELSE ' ';
INSTR/A300 = IF FLATTEN.LIST EQ 1 THEN INSTRUCTOR ELSE ' ';
SPRT/A3 = IF FLATTEN.LIST EQ 1 THEN SPRT ELSE ' ';
GRAD/A150 = IF FLATTEN.LIST EQ 1 THEN GRDTERM ELSE ' ';
END
-*A NEW LINE IS ADDED IN THIS SECTION AND SHOULD NOT BE THERE
TABLE FILE FLATTEN
SUM
MAX.CID AS 'ID'
MAX.FIRST AS 'FIRST'
MAX.LASTN AS 'LAST'
MAX.LVL AS 'SLEVEL'
MAX.ASTD AS 'ASTD'
MAX.HONORS AS 'HONORS'
MAX.CGPA AS 'CGPA'
MAX.TGPA AS 'TGPA'
MAX.CRN AS 'CRN'
MAX.COLVL AS 'CLVL'
MAX.SUBJ AS 'SUBJ'
MAX.CRSENUM AS 'CNUM'
MAX.CRSETERM AS 'CTERM'
MAX.CDATE AS 'CHANGE DATE'
MAX.GRADE AS 'NEW GRADE'
MAX.PREV AS 'PREV GRADE'
MAX.I? AS 'I?'
MAX.REASON AS 'REASON'
MAX.COMMENT AS 'COMMENT'
MAX.COL AS 'COLL'
MAX.DEPT AS 'DEPT'
MAX.INSTR AS 'INSTR'
MAX.SPRT AS 'SPRT'
MAX.GRAD AS 'GRAD TERM'
BY PERSON_UID NOPRINT
BY CRN NOPRINT
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
-******code stops*******************************

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

Can you provide sample data for NEWFILE2.

While using LIST, you have sorted only on 2 fields, Person and CRN. But there is no sort included to ensures final grade to get LIST value 1. You need to add another sort on either on GRADE_CHANGE_DATE or FINAL_GRADE_SEQUENCE_NUMBER based on your data.

Thanks,
Ram

-****newfile2 code here, a bunch of matches***
MATCH FILE STEP4
PRINT *
BY PERSON_UID
RUN
FILE GPA1
PRINT CGPA
BY PERSON_UID NOPRINT
RUN
FILE GPA2
PRINT TGPA
BY PERSON_UID NOPRINT
RUN
FILE SPORT1
SUM SPRT
BY PERSON_UID NOPRINT
RUN
FILE GRAD
PRINT GRDTERM
BY PERSON_UID NOPRINT
AFTER MATCH HOLD AS NEWFILE2 OLD
END
JOIN CLEAR *
JOIN PERSON_UID AND ACADEMIC_PERIOD AND COURSE_REFERENCE_NUMBER IN NEWFILE2 TO ALL PERSON_UID AND ACADEMIC_PERIOD AND COURSE_REFERENCE_NUMBER IN STUDENT_COURSE AS JN6S
JOIN PERSON_UID IN NEWFILE2 TO PERSON_UID IN PERSON_DETAIL AS JN7
-****code ends