I have a procedure that I need to function like this:
1. Create hold file of students enrolled in a previous term
2. Create hold file of students enrolled in current term
3. Join 2 hold files and produce a report of students that are in the 1st hold file and not in the 2nd.

My problem is that when I create my 2 hold files I need to parameterize on term and when I run my 2nd hold file it seems to always revert to the parameters from the 1st hold file.

Is this even possible and how do you set this up?

Malinda

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

I have some questions for you Malinda:

1. How are you invoking the piece of code that creates the HOLD file(s)? via -INCLUDE or EXEC?
2. How are you implementing the parameters?

This task could potentially be done with MATCH logic.

-* First perform whatever steps are necessary to determine current and previous terms - probably best via use
-* of Dialogue Manager statements that produce two ampersand variables (ex.: &CURR_TERM and &PRIOR_TERM).
.
.
MATCH FILE <filename>
 PRINT <column names>
 BY STUDENT_ID  (or whatever your key/s is/are)
 WHERE <term column> EQ '&PRIOR_TERM';  (remove single quotes if term column is numeric)
RUN
FILE <filename>
 PRINT <column names>
 BY STUDENT_ID
 WHERE <term column> EQ '&CURRENT_TERM';  (remove single quotes if term column is numeric)
 AFTER MATCH HOLD AS <holdname> OLD-NOT-NEW
END
-*
TABLE FILE <holdname>
 PRINT <column names>
 BY STUDENT_ID
END

Here is my code:

 TABLE FILE CSPRDSC_STDNT_ENRL
BY 'CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.EMPLID'
HEADING
""
FOOTING
""
WHERE CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STRM IN csprdsc_stdnt_enrl)).Select previous term EQ.;
WHERE ( CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STDNT_ENRL_STATUS EQ 'E' ) AND ( CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.CRSE_GRADE_OFF NE 'W' );
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCSTU1 FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.EMPLID' 
TABLE FILE CSPRDSC_STDNT_ENRL
BY 'CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.EMPLID'
HEADING
""
FOOTING
""
WHERE CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.INSTITUTION EQ 'CSC01';
WHERE CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STRM EQ &STRM.(OR(FIND STRM,CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STRM IN csprdsc_stdnt_enrl)).Select current/upcoming term EQ.;
WHERE ( CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.STDNT_ENRL_STATUS EQ 'E' ) AND ( CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.CRSE_GRADE_OFF NE 'W' );
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD AS CSCSTU2 FORMAT FOCUS INDEX 'CSPRDSC_STDNT_ENRL.CSPRDSC_STDNT_ENRL.EMPLID'  

Then I would take these 2 hold files and left outer join them and then include a where statement to pull the emplids from the first file that are not in the second.

Malinda, you are only using 1 parameter: &STRM. That's why both HOLD files are giving you the same information.

Use 2 different variables and your data should be correct. In addition, try Dan's suggestion about MATCH .. OLD-NOT-NEW as it looks more "natural" than trying to implement an A-minus-B logic using JOIN (which would require short-path and testing for MISSING values).

Oh, and if you use MATCH logic then you don't need your HOLD files to be FOCUS INDEX ....

Malinda, how many records would you obtain for each student when querying CSPRDSC_STDNT_ENRL in any given term? just one?

I still don't like the idea of querying the database *twice* to obtain pieces of data that could technically be retrieved in one single pass.

Would this work for you?

-DEFAULTS &PREV_TERM='2010-01';
-DEFAULTS &CURR_TERM='2010-02';
-*
-* Get students who were enrolled in the previous term, the current one or both
TABLE FILE CSPRDSC_STDNT_ENRL
SUM
COMPUTE IN_PREV_TERM_IND/A1 = IF STRM EQ '&PREV_TERM' THEN 'Y' ELSE 'N'; 
COMPUTE IN_CURR_TERM_IND/A1 = IF STRM EQ '&CURR_TERM' THEN 'Y' ELSE 'N'; 
BY EMPLID
WHERE STRM EQ '&PREV_TERM' OR 'CURR_TERM'
ON TABLE HOLD AS HSTUDENTS
END
-*
-* Print students enrolled in the previous term but not in the current one
TABLE FILE HSTUDENTS
PRINT EMPLID
WHERE IN_PREV_TERM EQ 'Y' AND IN_CURR_TERM EQ 'N'
END

That would be more efficient that going to the database twice and in addition you avoid using JOIN and/or MATCH altogether. I'll leave the auto-prompting or whatever approach you use to capture parameters up to you

Hope that helps,
- Neftali.

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

neftali,

Can you code this using the gui or have to use the text editor?

Malinda

Oh boy, what was I thinking? My brain seems to start shutting down Friday afternoons.

The code I posted earlier today makes no sense. I'll rework it and will post it back. As for your question, you should be able to use the GUI.

This should do what I had in mind:

-DEFAULTS &PREV_TERM='2010-01';
-DEFAULTS &CURR_TERM='2010-02';

DEFINE FILE CSPRDSC_STDNT_ENRL
IN_PREV_TERM_IND/I4 = IF STRM EQ '&PREV_TERM' THEN 1 ELSE 0; 
IN_CURR_TERM_IND/I4 = IF STRM EQ '&CURR_TERM' THEN 1 ELSE 0; 
END 
TABLE FILE CSPRDSC_STDNT_ENRL
SUM
        IN_PREV_TERM_IND
        IN_CURR_TERM_IND
BY EMPLID
WHERE STRM EQ '&PREV_TERM' OR 'CURR_TERM'
ON TABLE HOLD AS HSTUDENTS
END

TABLE FILE HSTUDENTS
PRINT EMPLID
WHERE IN_PREV_TERM GT 0 AND IN_CURR_TERM EQ 0
END

I agree, you should never go to your database/file multiple times if at all possible.

Glenda, I can see that your Production environment is 5.3.6. Suddenly, I don't feel that lonely anymore

Neftali, you're missing a whole pile of amazing features between 5.3.4 and 5.3.6!

I am?

neftali,

sorry it took me so long to respond...keep getting pulled away.

In coding this with the gui....is there a way so that other users could run this through the dashboard and select the prev term and current terms themselves or will this always have to be hard coded?

quote:

WHERE STRM EQ '&PREV_TERM' OR 'CURR_TERM'

Malinda, if Neftali's generic code works for you, there isn't any reason you couldn't prompt for the terms that you want on your report. Or you could prompt for one term and calculate the other in the program.

BTW, there is an ampersand missing in the above line of Neftali's code. It should be &CURR_TERM.

Oops, you're right Ginny! Thanks for pointing that out.

Thank you all! I think this just might work for me. I have some other values I need to work in as well but this gets me the main population of students I need. I will work from there!

Malinda