February 17, 2012, 10:41 AM
EuclidChoosing Output Formats
I am not sure if this is a matter of personal preference, but which hold file output type would be the best in the following situation?
I am joining Table 1 with Table 2 on Course_id. Then the final output file to the customer will be an xml file. Although I know I could just Table File Table2, get the fields I want and use a BY HIGHEST 1 ACADEMIC_PERIOD, I am just using this as an example for learning/demonstration purposes. I am trying to decide if I should hold Table 1 as ALPHA, FOCUS, or something else so I can join it to Table 2.
FieldsTable 1Course_Id A255V <-- Unique Values
Table 2Course_Id A255V
Academic_Period A63V
Subject_Desc A255V
Course_Title A255V
Course_Desc TX50
Course_Credit D20.2
Example Data TableTable 1|===========|
|Course_Id |
|===========|
|ENGL200 001|
|CSCI101A101|
|WRIT101 001|
|ENGL201 001|
|===========|
Table 1 contains a master list of course_id, all unique, max 1000 records.
Table 2|===============================================================================|
|Course_Id |Subject_Desc|Course_Title|Course_Desc|Course_Credit|Academic_Period|
|===============================================================================|
|ENGL200 001|English |American Lit|Long Text |3.0 |201110 |
|CSCI101A101|Comp Sci |MS Word Lab |Long Text |0.5 |201110 |
|WRIT101 001|Writing |Writing 1 |Long Text |3.0 |201180 |
|ENGL201 001|English |Euro Lit |Long Text |3.0 |201180 |
|ENGL201 001|English |Euro Lit |Long Text |4.0 |201210 |
|===============================================================================|
Table 2 contains 10000+ records with duplicate course_id but different academic semesters, course_desc, course_title, and about 50 other fields.
Edit: Forum messed up formatting, not surprised in the least bit, but I tried to fix it the best I could.
February 17, 2012, 01:34 PM
GCohenI assume both Table1 and Table2 are not database files where you can just join them. But I assume they are just flat sequential files on the disk. In this case the best thing to do is a MATCH.
MATCH FILE TABLE1
SUM COURSE_ID NOPRINT BY COURSE_ID
RUN
FILE TABLE2
PRINT ACEDEMIC_PERIOD ...etc
BY COURSE_ID
ON TABLE HOLD FORMAT XML AS MYFILE
WHERE ... if needed...;
END
February 17, 2012, 01:45 PM
EuclidThank you for the reply. You are correct they are not database files. MATCH never crossed my mind, thank you for the idea! To add on to your point of not being database files, I could easily do it with a SQL statement, but our sql pass-through access was and is turnned off.