show off...how about keep my job . I've been asked to product this report since monday. I guess i'll pull two reports and merge them LOL!!! now how does that help the others who want to pull this ....go figure.

OK, I'll try to help:

1ST OFF - This is SCT BANNER / Student - Need to reverse the JOINs

 
JOIN FILE SLRRASG AT SLRRASG_PIDM TAG T1 TO 
ALL  FILE SLRMASG AT SLRMASG_PIDM TAG T2
WHERE T1.SLRRASG_PIDM EQ T2.SLRMASG_PIDM; 
WHERE T2.SLRMASG_TERM_CODE EQ '200809';
END
-RUN
JOIN FILE SLRRASG AT SLRRASG_PIDM TAG T1 TO 
ALL  FILE AT_AR_BALANCE_BY_ENTITY AT PIDM_KEY TAG T3
WHERE T1.SLRRASG_PIDM EQ T3.PIDM_KEY; 
WHERE T1.SLRRASG_ASCD_CODE EQ 'AC';
WHERE T1.SLRRASG_TERM_CODE EQ '&SLRRASG_TERM_CODE.Term Code.';
END
-RUN

I've never tried a PROMPT in a JOIN, see if the above works. If not put it down below.

Now, report off of SLRRASG

 
DEFINE FILE SLRRASG
Name/A40V=LAST_NAME || (', ' | FIRST_NAME) || (' ' | MIDDLE_INITIAL);
END
TABLE FILE SLRRASG
BY Name NOPRINT
BY ID
BY Name
BY SLRRASG_TERM_CODE AS 'Term Code'
BY SLRMASG_MRCD_CODE AS 'Meal Plan'
BY SLRMASG_BEGIN_DATE AS 'Meal Begin Date'
BY SLRMASG_END_DATE AS 'Meal End Date'
BY SLRMASG_AR_IND AS 'Meal AR IND'
BY SLRRASG_BLDG_CODE AS 'Building'
BY SLRRASG_ROOM_NUMBER AS 'Room No.'
BY SLRRASG_BEGIN_DATE AS 'Room Begin Date'
BY SLRRASG_END_DATE AS 'Room End Date'
BY AMOUNT_DUE AS 'Amount Due'
BY MEMO_BALANCE AS 'Memo Balance' 

Comment these out, they are now done in the JOIN
May need to put the 1st one back in

-*WHERE SLRRASG_TERM_CODE EQ '&SLRRASG_TERM_CODE.Term Code.';
-*WHERE SLRRASG_ASCD_CODE EQ 'AC';
-*WHERE SLRMASG_TERM_CODE EQ '200809';

 

See if this works for you...

Tom

quote:

WHERE SLRRASG_TERM_CODE EQ '&SLRRASG_TERM_CODE.Term Code.';
WHERE SLRRASG_ASCD_CODE EQ 'AC';
WHERE SLRMASG_TERM_CODE EQ '200809';

I think the problem is in your where statement. Try changing this to:

 
WHERE (SLRRASG_TERM_CODE EQ '&SLRRASG_TERM_CODE.Term Code.' 
 AND SLRRASG_ASCD_CODE EQ 'AC' )
 OR SLRMASG_TERM_CODE EQ '200809'
 

You might also want to change some of your BY statements and put them in a PRINT instead.

SET ALL = PASS

Includes parent instances that are missing descendants, even if WHERE or IF criteria exist to screen fields in the descendant segments that are missing instances (that is, a test on a missing segment passes).

This will override the WHERE clauses for the short path, but has the side effect of giving you the people without rooms or meals also. You will need to HOLD and get rid of them.

I think that Mickey's suggestion of using MATCH is the best - the MATCH wizard in DS makes it relatively painless.

Cheers

Stu

I agree, use match, as much as I hate to do so, sometimes it's the only way to merge your data. The GUI is good for match.

Occasionally or often depending on requirements use the GUI to get what you want, then fine tune it by editing the code. Remembering though if you do edit, the GUI is notorious for removing your fine tuning.