Hello. I am trying to filter out only students that have any transfer/repeat credit at all. If so, give me these students. Any ideas how I can do that?

SET ASNAMES = ON
-*-DEFAULT &BEGIN_DATE_YYYYMMDD = '20090601'
-*-DEFAULT &END_DATE_YYYYMMDD = '20090821'
TABLE FILE STUDENT_COURSE
SUM
PERSON_UID NOPRINT
COURSE_IDENTIFICATION NOPRINT
ACADEMIC_PERIOD
BY PERSON_UID
BY COURSE_IDENTIFICATION AS 'COURSE_INDEX'
-*WHERE COURSE_LEVEL NE 'GR'
WHERE ACADEMIC_PERIOD EQ '&TERM'
WHERE GRADE_TYPE NE 'R'
WHERE FINAL_GRADE IS MISSING OR FINAL_GRADE NE 'W'
WHERE COURSE_IDENTIFICATION NOT LIKE 'TRNF00%'
WHERE COURSE_IDENTIFICATION NOT LIKE 'GENL%'
WHERE COURSE_IDENTIFICATION NOT LIKE '%0001'
-*WHERE TRANSFER_COURSE_INSTITUTION NE '999979 OR 999973' OR TRANSFER_COURSE_INSTITUTION IS MISSING
-*WHERE ACADEMIC_PERIOD NE ACADEMIC_PERIOD
ON TABLE HOLD AS MAMINDEX1
END
-*******************************************************************************************************
JOIN ACADEMIC_PERIOD AND PERSON_UID IN MAMINDEX1 TO ACADEMIC_PERIOD AND PERSON_UID IN ACADEMIC_STUDY
TABLE FILE MAMINDEX1
PRINT
PERSON_UID NOPRINT
COURSE_IDENTIFICATION NOPRINT
ENROLLMENT_ADD_DATE
BY PERSON_UID
BY COURSE_IDENTIFICATION AS 'COURSE_INDEX'
-*WHERE ENROLLMENT_ADD_DATE FROM &BEGIN_DATE_YYYYMMDD TO &END_DATE_YYYYMMDD;
-*WHERE COURSE_LEVEL NE 'GR'
-*WHERE ACADEMIC_PERIOD EQ '&TERM'
WHERE STUDENT_LEVEL EQ 'UG'
ON TABLE HOLD AS MAMINDEX2
END
-**********************************************************************************************************
JOIN CLEAR *
JOIN PERSON_UID AND COURSE_INDEX IN MAMINDEX2 TO ALL PERSON_UID AND COURSE_IDENTIFICATION IN STUDENT_COURSE
-*count if the student has already taken a class 2 or more times. a currently registered course and the same course from
-*previous term
DEFINE FILE MAMINDEX2
CNTR/I2 = 1
END
TABLE FILE MAMINDEX2
SUM
PERSON_UID
ACADEMIC_PERIOD
COURSE_IDENTIFICATION
CNTR
LST.FINAL_GRADE
ENROLLMENT_ADD_DATE
TRANSFER_COURSE_IND
BY PERSON_UID
BY COURSE_IDENTIFICATION
-*WHERE COURSE_LEVEL NE 'GR'
WHERE GRADE_TYPE NE 'R'
WHERE FINAL_GRADE IS MISSING OR FINAL_GRADE NE 'W'
WHERE COURSE_IDENTIFICATION NOT LIKE 'TRNF00%'
WHERE COURSE_IDENTIFICATION NOT LIKE 'GENL%'
WHERE COURSE_IDENTIFICATION NOT LIKE '%0001'
-*WHERE TRANSFER_COURSE_INSTITUTION NE '999979 OR 999973' OR TRANSFER_COURSE_INSTITUTION IS MISSING
ON TABLE HOLD AS MAMCOURSES1
-*BY ACADEMIC_PERIOD
-*WHERE COURSE_INDEX EQ COURSE_IDENTIFICATION
END
-************************************************************************************************************
TABLE FILE MAMCOURSES1
SUM
PERSON_UID
ACADEMIC_PERIOD
COURSE_IDENTIFICATION
CNTR
ENROLLMENT_ADD_DATE
TRANSFER_COURSE_IND
BY PERSON_UID
BY COURSE_IDENTIFICATION
ON TABLE HOLD AS MAMCOURSES15
END
TABLE FILE MAMCOURSES15
PRINT
PERSON_UID
COURSE_IDENTIFICATION NOPRINT
CNTR
ENROLLMENT_ADD_DATE
TRANSFER_COURSE_IND
BY PERSON_UID
BY COURSE_IDENTIFICATION AS 'CRSE_INDEX2'
WHERE CNTR GE 2
WHERE TRANSFER_COURSE_IND EQ 'Y'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT
ON TABLE HOLD AS MAMINDEX25
END
-*********************************************************************************************************
JOIN CLEAR *
JOIN PERSON_UID AND CRSE_INDEX IN MAMINDEX25 TO ALL PERSON_UID AND COURSE_IDENTIFICATION IN STUDENT_COURSE
TABLE FILE MAMINDEX25
PRINT
ACADEMIC_PERIOD AS 'TERM'
COURSE_IDENTIFICATION AS 'COURSE'
LST.FINAL_GRADE AS 'GRADE'
TRANSFER_COURSE_INSTITUTION AS 'TRANSFER_SCHOOL_CODE'
TRANSFER_COURSE_INST_DESC AS 'TRANSFER_SCHOOL'
GRADE_TYPE NOPRINT
COURSE_SECTION_NUMBER
ENROLLMENT_ADD_DATE
TRANSFER_COURSE_IND
BY PERSON_UID
BY NAME
BY ID
BY CRSE_INDEX2 NOPRINT
BY HIGHEST ACADEMIC_PERIOD NOPRINT
-*WHERE TRANSFER_COURSE_INSTITUTION NE '999979 OR 999973' OR TRANSFER_COURSE_INSTITUTION IS MISSING
WHERE FINAL_GRADE IS MISSING OR FINAL_GRADE NE 'W';
WHERE GRADE_TYPE NE 'R';
WHERE TRANSFER_COURSE_IND EQ 'Y'
ON TABLE HOLD AS MAMCOURSES2
END
-***********************************************************************************************
JOIN CLEAR *
JOIN PERSON_UID IN MAMCOURSES2 TO ALL PERSON_UID IN ACADEMIC_OUTCOME
TABLE FILE MAMCOURSES2
SUM
PERSON_UID NOPRINT
ACADEMIC_PERIOD_GRADUATION NOPRINT
STATUS
GRADUATION_STATUS
ENROLLMENT_ADD_DATE
BY PERSON_UID
BY ACADEMIC_PERIOD_GRADUATION
-*WHERE STUDENT_LEVEL NE 'GR'
WHERE STATUS EQ 'AW'
-*WHERE GRADUATION_STATUS EQ 'AW'
ON TABLE HOLD AS MAMGRAD1
END
MATCH FILE MAMCOURSES2
PRINT
PERSON_UID NOPRINT
BY PERSON_UID
-*BY ID
BY NAME
BY ID
BY COURSE
BY TERM
BY GRADE
BY TRANSFER_SCHOOL_CODE
BY TRANSFER_SCHOOL
BY GRADE_TYPE
BY COURSE_SECTION_NUMBER
BY ENROLLMENT_ADD_DATE
-*BY GRADUATION_STATUS
-*BY STATUS
RUN
FILE MAMGRAD1
PRINT ACADEMIC_PERIOD_GRADUATION
BY PERSON_UID NOPRINT
BY STATUS
BY GRADUATION_STATUS
AFTER MATCH HOLD AS MAMCOURSES3 OLD
END
TABLE FILE MAMCOURSES3
PRINT
PERSON_UID NOPRINT
STATUS
ACADEMIC_PERIOD_GRADUATION AS 'GRAD_TERM'
COURSE
TERM
GRADE
TRANSFER_SCHOOL_CODE AS 'SCHOOL_CODE'
TRANSFER_SCHOOL
COURSE_SECTION_NUMBER
ENROLLMENT_ADD_DATE
BY NAME
BY ID
BY COURSE NOPRINT
BY HIGHEST TERM NOPRINT
ON TABLE HOLD AS MAMCOURSES4
END
JOIN CLEAR *
JOIN PERSON_UID IN MAMCOURSES4 TO ALL PERSON_UID IN ADMISSIONS_APPLICATION
TABLE FILE MAMCOURSES4
SUM
PERSON_UID NOPRINT
STATUS
GRAD_TERM
COURSE
TERM
GRADE
SCHOOL_CODE
TRANSFER_SCHOOL
LST.ACADEMIC_PERIOD AS 'ADMIT_TERM'
ENROLLMENT_ADD_DATE AS 'ENROLLED_DATE'
BY NAME
BY ID
BY COURSE NOPRINT
BY HIGHEST TERM NOPRINT
BY SCHOOL_CODE NOPRINT
BY COURSE_SECTION_NUMBER NOPRINT
BY ENROLLMENT_ADD_DATE NOPRINT
WHERE ADMISSIONS_APPLICATION.STUDENT_LEVEL EQ 'UG'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.250000,
RIGHTMARGIN=0.250000,
TOPMARGIN=0.250000,
BOTTOMMARGIN=0.250000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='TIMES NEW ROMAN',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
ENDSTYLE
END

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

Hi - Welcome to the Forum.

You look like you have a report...
we need more information on what you are trying to filter on.

Which Fields and what are they supposed to be?

With your compound "not" logic you may want to create a truth table. remember multiply where get combines as if there are "AND"s between them.

Some times it is cleaner to do the logic in a define and only use the where on the result of the define.

You may want to look at WHERE TOTAL in the documentation. This is like an SQL HAVING.

Hello. This code as posted pulls students who have repeat course credit for a given term. It also includes if the courses are transfer courses. I would ultimately like for all the students with transfer course credit to be shown, eliminating students with regular UNCC repeat course credit. I hope that makes sense.

How do you expect us who know nothing about your data to figure out what is essentially a business problem?

"students who have repeat course credit for a given term. It also includes if the courses are transfer courses. I would ultimately like for all the students with transfer course credit to be shown, eliminating students with regular UNCC repeat course credit" is not the kind of stuff a programming language forum usually untangles.

How do I "program" in my code that if a student has any transfer credit at all, give me their record? If not, eliminate the student from the output?

So, if Student A has any TRANSFER_COURSE_IND EQ 'Y', pull their entire repeat course record.

As Francis said, we know nothing about your data or data structure.
But a small help could be to create a defined field that holds a 1 for every student that has transfer_couurse_ind set to Y and 0 if that's not the case. Then create a multi verb request like

DEFINE FILE MAMINDEX2
CNTR/I2 = 1;
TRANSFER_CREDIT/I4 = IF TRANSFER_COURSE_IND EQ 'Y' THEN 1 ELSE 0;
END
TABLE FILE MAMINDEX2
SUM TRANSFER_CREDIT
BY PERSON_UID

SUM PERSON_UID ACADEMIC_PERIOD COURSE_IDENTIFICATION CNTR LST.FINAL_GRADE ENROLLMENT_ADD_DATE TRANSFER_COURSE_IND
BY PERSON_UID
BY COURSE_IDENTIFICATION

Now, for every student in MAMCOURSES1 that has a transfer_credit value larger than 0 you can pull the repeat records.
Again, this is just my guess, since I have no insight in your data (structures).
Still I hope this helps a bit...

Thank you much.