Code:

 TABLE FILE SACRSFNL
SUM 
     'SACRSFNL.SEG01.TERM'
     'SACRSFNL.SEG01.SITE_ID'
     'SACRSFNL.SEG01.START_DT/YY-M-D'
     'SACRSFNL.SEG01.TITLE'
     'SACRSFNL.SEG01.COURSE_ID'
     'SACRSFNL.SEG01.DESCR'
     'SACRSFNL.SEG01.PUBLISH'
     'SACRSFNL.SEG01.END_DT/YY-M-D'
     'SACRSFNL.SEG01.TEMPLATE'
BY 'SACRSFNL.SEG01.COURSE_ID' NOPRINT
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT COMMA
ON TABLE SET HTMLCSS ON 

I am getting it to output okay with the correct lines but in the term spot, probably since it is summed, it only prints the first line value and all others are blank. How can I force it to print term on each row?

malinda

Try adding:

ON TABLE SET BYDISPLAY ON to your procedure to print repeating values.

Norb

I still get:

 "                                                  ","1108.11613 ",20100823,""ACTG     241       79A1-8W1-ACCOUNTING PRINCIPLES I"       ","ACTG     241       79A1-8W1","ACCOUNTING PRINCIPLES I       ","0",20101015,"template.online          "
"                                                  ","1108.22184 ",20101025,""BA       241       79B1-8W2-QUANTITATIVE METHODS"          ","BA       241       79B1-8W2","QUANTITATIVE METHODS          ","0",20101217,"template.online          "
"                                                  ","1108.10549 ",20100823,""HIST     231       0080-1-US HISTORY TO 1877"              ","HIST     231       0080-1  ","US HISTORY TO 1877            ","0",20101217,"template.webenhanced     "
"                                                  ","1108.12665 ",20100823,""MATH     232       0081-1-APPLIED STATISTICS"              ","MATH     232       0081-1  ","APPLIED STATISTICS            ","0",20101217,"template.webenhanced     "
"                                                  ","1108.10999 ",20100823,""PSYC     433       0080-1-ABNORMAL PSYCHOLOGY"             ","PSYC     433       0080-1  ","ABNORMAL PSYCHOLOGY           ","0",201012 

SET BYDISPLAY=ON would display repeated values on the BY field, but "TERM" is a regular display field so BYDISPLAY wouldn't help you there.

Please change the request to PRINT and show us what data is being returned to you. Also, don't forget to enclose it in

[code]

tags for ease of reading.

Here is the info printed. I put it in the tags so hopefully that works too.

 "Fall 2010 1st 8 Weeks                             ","1108.11613 ",20100823,""ACTG     241       79A1-8W1-ACCOUNTING PRINCIPLES I"       ","ACTG     241       79A1-8W1","ACCOUNTING PRINCIPLES I       ","0",20101015,"template.online          "
"                                                  ","1108.11613 ",20100823,""ACTG     241       79A1-8W1-ACCOUNTING PRINCIPLES I"       ","ACTG     241       79A1-8W1","ACCOUNTING PRINCIPLES I       ","0",20101015,"template.online          "
"Fall 2010 2nd 8 Weeks                             ","1108.12184 ",20101025,""BA       241       79B1-8W2-QUANTITATIVE METHODS"          ","BA       241       79B1-8W2","QUANTITATIVE METHODS          ","0",20101217,"template.online          "
"                                                  ","1108.12184 ",20101025,""BA       241       79B1-8W2-QUANTITATIVE METHODS"          ","BA       241       79B1-8W2","QUANTITATIVE METHODS          ","0",20101217,"template.online          "
"                                                  ","1108.22184 ",20101025,""BA       241       79B1-8W2-QUANTITATIVE METHODS"          ","BA       241       79B1-8W2","QUANTITATIVE METHODS          ","0",20101217,"template.online          "
"Fall 2010                                         ","1108.12318 ",20100823,""HIST     231       0080-1-US HISTORY TO 1877"              ","HIST     231       0080-1  ","US HISTORY TO 1877            ","0",20101217,"template.webenhanced     "
"                                                  ","1108.12318 ",20100823,""HIST     231       0080-1-US HISTORY TO 1877"              ","HIST     231       0080-1  ","US HISTORY TO 1877            ","0",20101217,"template.webenhanced     "
"                                                  ","1108.10549 ",20100823,""HIST     231       0080-1-US HISTORY TO 1877"              ","HIST     231       0080-1  ","US HISTORY TO 1877            ","0",20101217,"template.webenhanced     "
"Fall 2010                                         ","1108.12665 ",20100823,""MATH     232       0081-1-APPLIED STATISTICS"              ","MATH     232       0081-1  ","APPLIED STATISTICS            ","0",20101217,"template.webenhanced     "
"                                                  ","1108.12665 ",20100823,""MATH     232       0081-1-APPLIED STATISTICS"              ","MATH     232       0081-1  ","APPLIED STATISTICS            ","0",20101217,"template.webenhanced     "
"Fall 2010                                         ","1108.12619 ",20100823,""PSYC     433       0080-1-ABNORMAL PSYCHOLOGY"             ","PSYC     433       0080-1  ","ABNORMAL PSYCHOLOGY           ","0",20101217,"template.webenhanced     "
"                                                  ","1108.12619 ",20100823,""PSYC     433       0080-1-ABNORMAL PSYCHOLOGY"             ","PSYC     433       0080-1  ","ABNORMAL PSYCHOLOGY           ","0",20101217,"template.webenhanced     "
"                                                  ","1108.10999 ",20100823,""PSYC     433       0080-1-ABNORMAL PSYCHOLOGY"             ","PSYC     433       0080-1  ","ABNORMAL PSYCHOLOGY           ","0",20101217,"template.webenhanced     "
 

If TERM is an alpha field, by summing it you are only getting the last value in the sort group COURSE_ID.

I recommend adding a sort (BY) on TERM with BYDISPLAY set to on and taking the column out of the SUM part of the report.

Malinda, based on the data you posted which was obtained by means of TABLE FILE PRINT, I can see that some of the values for term are actually blank. Why is that?

Ginny's suggestion is usually the way to go but in your particular case I'm afraid you'd still have issues considering the blank or MISSING values you have for the TERM field in your source data.

Give it a try anyway to see what you get. If it still does not work, try something like:

TABLE FILE SACRSFNL
SUM 
     MAX.TERM  <-- Note the MAX. prefix here!!
     SITE_ID
     START_DT/YY-M-D
     TITLE
     COURSE_ID
     DESCR
     PUBLISH
     END_DT/YY-M-D
     TEMPLATE
BY COURSE_ID NOPRINT
END

As any "printable" alphabetic and numeric character have higher ASCII codes than spaces, getting the MAX.TERM in each group will show you any TERM value that is not blank within each COURSE_ID.

If you have changed to print and you're still getting blank rows, my suggestion is sto remove the NOPRINT from the course_id field. You'll probably see that there are different values which is causing the output you are getting. It appears that all the other field values are identical, so if you just summed them up, the alpha field course_id would be displaying the last value of the field within the sort group which appears to be blank.

And if those rows are truly duplicated, just eliminate every record which has TERM EQ ' ' and you should get one of each with a correct course_id and term.

It worked to add the max. I am thinking that the blank terms are coming in with a previous join....can't figure out logically why they are blank but it duplicates it based on how many sequence numbers for the class note are associated with that class. Lists the first as the term and all others as blank.

Is it better to use the max or the where term ne ' ' statement?

Malinda

One thing that I am going to try is doing each data set in its own hold file and then joining those instead of getting 1 hold file, then joining back to add in the rest - which I think is what is causing all the duplication. I will try this and let you know what I get.

Malinda

It's probably better to eliminate unwanted records (WHERE TERM NE ' '), especially if this will allow you to change the verb from SUM to PRINT. SUMming may produce unwanted results if there aren't enough BY phrases and/or prefix operators (like MAX.) to prevent the inappropriate aggregation of numeric columns or the incorrect reduction of alphanumeric columns to the last value.

The only thing about the where is that it actually gets rid of the line that I want to keep. The site_id that I want to keep is usually the last one listed but it usually has a blank term associated with it.

quote:

I am thinking that the blank terms are coming in with a previous join....can't figure out logically why they are blank but it duplicates it based on how many sequence numbers for the class note are associated with that class.

Then add an additional line in that part of the extract to have the HIGHEST 1 CLASS_NOTE (or what ever the field is called) so that you then have a ?? to 1 join when adding this hold file into your join(s).

T

Here is code from creating each set in its own hold file, joining those and outputing. It does not creating the duplicate/blank terms like before but I am now having problems getting the right site_id.

JOIN
 LEFT_OUTER CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM IN CSQASC_CLASS_TBL
 TO MULTIPLE CSQASC_TERM_VAL_TBL.CSQASC_TERM_VAL_TBL.STRM IN CSQASC_TERM_VAL_TBL
 TAG J0 AS J0
 END
JOIN
 LEFT_OUTER CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CRSE_ID
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CRSE_OFFER_NBR
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE
 AND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION IN CSQASC_CLASS_TBL
 TO MULTIPLE CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CRSE_ID
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CRSE_OFFER_NBR
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.STRM
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.SESSION_CODE
 AND CSQASC_CLASS_NOTES.CSQASC_CLASS_NOTES.CLASS_SECTION IN CSQASC_CLASS_NOTES
 TAG J4 AS J4
 END
DEFINE FILE CSQASC_CLASS_TBL
CALL_NO_CLASS/A5=EDIT (CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_NBR, '99999');
SITE_ID_1/A11=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM||'.'||CALL_NO_CLASS;
SESSION/A11=
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '8W1' THEN '1st 8 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '8W2' THEN '2nd 8 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W1' THEN '1st 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W2' THEN '2nd 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '5W3' THEN '3rd 5 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W1' THEN '1st 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W2' THEN '2nd 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '4W3' THEN '3rd 4 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W1' THEN '1st 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W2' THEN '2nd 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W3' THEN '3rd 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W4' THEN '4th 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W5' THEN '5th 2 Weeks' ELSE
IF CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE EQ '2W6' THEN '6th 2 Weeks' ELSE ' ';
COURSE_ID/A27=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION || '-' || CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE;
COURSE_ID_1/A24=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION;
SEM/A1=EDIT ( CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM , '$$$9');
SECTION_NO/A2=EDIT (CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION, '99$$');
TERM/A50=
IF ((COURSE_ID_1 EQ 'COLG010 01') AND (SEM EQ '8' OR '1')) THEN '2010 Year Long' ELSE
IF (SECTION_NO EQ '81' AND SEM EQ '8') THEN 'Fall 2010 Correspondence' ELSE
IF (SECTION_NO EQ '81' AND SEM EQ '1') THEN 'Spring 2011 Correspondence' ELSE
IF SECTION_NO EQ '80' THEN 'Year Long Correspondence' ELSE J0.CSQASC_TERM_VAL_TBL.DESCR||(' '| SESSION);
TITLE/A60='"'||CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.DESCR||'"';
DESC/A29='"'||CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SUBJECT | CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CATALOG_NBR |' '| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CLASS_SECTION ||'-'|| CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SESSION_CODE||'"';
PUBLISH/A1='0';
TEMPLATE/A25=
IF SECTION_NO EQ '79' OR '89' OR '99' THEN 'template.online' ELSE
IF SECTION_NO EQ '80' THEN 'template.corr.yearlong' ELSE
IF SECTION_NO EQ '81' THEN 'template.corr.semester' ELSE 'template.webenhanced';
CALL_NO_SEQ/A5=EDIT ( J4.CSQASC_CLASS_NOTES.DESCRLONG, '99999$*');
SITE_ID_2/A11=CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM||'.'|| CALL_NO_SEQ;
END
TABLE FILE CSQASC_CLASS_TBL
PRINT 
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM'
     'J0.CSQASC_TERM_VAL_TBL.TERM'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.SITE_ID_1'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.CALL_NO_CLASS'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.START_DT'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.TITLE'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.COURSE_ID'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.DESCR'
     PUBLISH
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.END_DT'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.TEMPLATE'
HEADING
""
FOOTING
""
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.INSTITUTION EQ 'CSC01';
-*WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ &STRM.(OR(FIND CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM,CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM IN csqasc_class_tbl)).Select term.;
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ '1108'
WHERE J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR EQ '0025';
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD AS SACRS FORMAT FOCUS INDEX 'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.COURSE_ID' 

TABLE FILE CSQASC_CLASS_TBL
PRINT 
     'J0.CSQASC_TERM_VAL_TBL.TERM'
     'J4.CSQASC_CLASS_NOTES.SITE_ID_2'
	 'J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR'
	 'J4.CSQASC_CLASS_NOTES.CLASS_NOTES_SEQ'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.START_DT'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.TITLE'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.COURSE_ID'
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.DESCR'
     PUBLISH
     'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.END_DT'
HEADING
""
FOOTING
""
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.INSTITUTION EQ 'CSC01';
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ '1108'
WHERE ( J4.CSQASC_CLASS_NOTES.CLASS_NOTE_NBR EQ ' ' ) AND ( J4.CSQASC_CLASS_NOTES.CLASS_NOTES_SEQ EQ 20 );
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD AS SEQCRS FORMAT FOCUS INDEX 'CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.COURSE_ID'
JOIN
 LEFT_OUTER SACRS.SEG01.COURSE_ID IN SACRS TO MULTIPLE SEQCRS.SEG01.COURSE_ID
 IN SEQCRS TAG J1 AS J1
 END
DEFINE FILE SACRS
SITE_ID/A11=IF ( J1.SEG01.CLASS_NOTE_NBR EQ ' ' AND J1.SEG01.CLASS_NOTES_SEQ EQ 20) THEN J1.SEG01.SITE_ID_2 ELSE SACRS.SEG01.SITE_ID_1;
-*SITE_ID/A11=IF SITE_ID_2 EQ ' ' THEN SITE_ID_1 ELSE SITE_ID_2;
END
TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'J1.SEG01.SITE_ID'
     'SACRS.SEG01.SITE_ID_1'
     'J1.SEG01.SITE_ID_2'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE'
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML   

Here is what I get......The correct number of records but not doing the define correctly in my last file.

TERM SITE_ID SITE_ID_1 SITE_ID_2 Start Date TITLE COURSE_ID Descr PUBLISH End Date TEMPLATE 
Fall 2010 1108.10999 1108.12619 1108.10999 2010/08/23 "PSYC 433 0080-1-ABNORMAL PSYCHOLOGY" PSYC 433 0080-1 ABNORMAL PSYCHOLOGY 0 2010/12/17 template.webenhanced 
Fall 2010   1108.12665   2010/08/23 "MATH 232 0081-1-APPLIED STATISTICS" MATH 232 0081-1 APPLIED STATISTICS 0 2010/12/17 template.webenhanced 
Fall 2010 1108.10549 1108.12318 1108.10549 2010/08/23 "HIST 231 0080-1-US HISTORY TO 1877" HIST 231 0080-1 US HISTORY TO 1877 0 2010/12/17 template.webenhanced 
Fall 2010 2nd 8 Weeks 1108.22184 1108.12184 1108.22184 2010/10/25 "BA 241 79B1-8W2-QUANTITATIVE METHODS" BA 241 79B1-8W2 QUANTITATIVE METHODS 0 2010/12/17 template.online 
Fall 2010 1st 8 Weeks   1108.11613   2010/08/23 "ACTG 241 79A1-8W1-ACCOUNTING PRINCIPLES I" ACTG 241 79A1-8W1 ACCOUNTING PRINCIPLES I 0 2010/10/15 template.online 
  

Sorry it didn't format it right in the output part....I put it in the tags

Try changing your final TABLE request to this:

TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'SITE_ID'
     'SACRS.SEG01.SITE_ID_1'
     'J1.SEG01.SITE_ID_2'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE'
.
.
.

When I try that it keeps switching it....

DEFINE FILE SACRS
SITE_ID/A11=IF SITE_ID_2 EQ ' 'THEN SITE_ID_1 ELSE SITE_ID_2;
END
TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'SACRS.SEG01.SITE_ID_1'
     'SACRS.SEG01.SITE_ID_1'
     'J1.SEG01.SITE_ID_2'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE'  

Then try changing the DEFINE name, so it cannot be confused with the two real columns:

DEFINE FILE SACRS
 SITE_IDX/A11=IF SITE_ID_2 EQ ' 'THEN SITE_ID_1 ELSE SITE_ID_2;
END
-*
TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'SITE_IDX'
     'SACRS.SEG01.SITE_ID_1'
     'J1.SEG01.SITE_ID_2'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE'

Still no luck....I have tried several different ways of arranging all my defines. I think the problem is that my site_id_2 (which is the one I want if there is actually a value in it) doesn't actually have a blank column. I tried ' ', 'null', 'blank'......and it still always pulls the site_id_2.

malinda

'null' and 'blank' are just constant string values that have no specific meaning in WF other than the 'null' or 'blank' values themselves. Did you try MISSING?

If you're attempting to read a field in a cross-referenced table which is included there as an result of an outer join then it is possible that, if there are no matching records in the "referenced" table, all of its fields in that particular instance will have a NULL value which in WF is treated as MISSING.

Syntax:

IF MY_FIELD [IS | IS NOT] MISSING THEN ...

Would using SET ALL=ON or PASS do the same thing?

I tried both the missing and the set all commands and am still not getting it.

JOIN
 LEFT_OUTER SACRS.SEG01.COURSE_ID IN SACRS TO MULTIPLE SEQCRS.SEG01.COURSE_ID
 IN SEQCRS TAG J1 AS J1
 END
DEFINE FILE SACRS
SITE_ID_CLASS/A11=SACRS.SEG01.STRM ||'.'||CALL_NO_CLASS;
SITE_ID_SEQ/A11=J1.SEG01.STRM ||'.'|| CALL_NO_SEQ;
SITE_IDX/A11=IF SITE_ID_SEQ IS MISSING THEN SITE_ID_CLASS ELSE SITE_ID_SEQ;
END
TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'J1.SEG01.SITE_IDX'
     'SACRS.SEG01.SITE_ID_CLASS'
     'J1.SEG01.SITE_ID_SEQ'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE'  

You'd need to evaluate for MISSING on fields you're reading from the database, not on virtual fields you are creating in the DEFINE block.

Evaluate J1.SEG01.STRM and see what you'll get.

Seem to still get the same result:

SET ALL = PASS
JOIN
 LEFT_OUTER SACRS.SEG01.COURSE_ID IN SACRS TO MULTIPLE SEQCRS.SEG01.COURSE_ID
 IN SEQCRS TAG J1 AS J1
 END
DEFINE FILE SACRS
SITE_ID_CLASS/A11=SACRS.SEG01.STRM ||'.'||CALL_NO_CLASS;
SITE_ID_SEQ/A11=J1.SEG01.STRM ||'.'|| CALL_NO_SEQ;
SITE_IDX/A11=IF J1.SEG01.STRM IS MISSING THEN SITE_ID_CLASS ELSE SITE_ID_SEQ;
END
TABLE FILE SACRS
PRINT 
     'SACRS.SEG01.TERM'
     'J1.SEG01.SITE_IDX'
     'SACRS.SEG01.SITE_ID_CLASS'
     'J1.SEG01.SITE_ID_SEQ'
     'SACRS.SEG01.START_DT'
     'SACRS.SEG01.TITLE'
     'SACRS.SEG01.COURSE_ID'
     'SACRS.SEG01.DESCR'
     'SACRS.SEG01.PUBLISH'
     'SACRS.SEG01.END_DT'
     'SACRS.SEG01.TEMPLATE' 

quote:

WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ '1108'
WHERE CSQASC_CLASS_TBL.CSQASC_CLASS_TBL.STRM EQ '1108'

SITE_IDX/A11=IF J1.SEG01.STRM IS MISSING THEN SITE_ID_CLASS ELSE SITE_ID_SEQ;

Based on the WHERE clauses (above) you have for your two extracts, field J1.SEG01.STRM in the DEFINE above for SITE_IDX will never be missing because it will always have a value of '1108'.

Previously, you were using the DEFINEs below. In this case DEFINEd field SITE_ID_SEQ will never be missing because if will always have a period (.) at the very least.

quote:

SITE_ID_CLASS/A11=SACRS.SEG01.STRM ||'.'||CALL_NO_CLASS;
SITE_ID_SEQ/A11=J1.SEG01.STRM ||'.'|| CALL_NO_SEQ;
SITE_IDX/A11=IF SITE_ID_SEQ IS MISSING THEN SITE_ID_CLASS ELSE SITE_ID_SEQ;

I was able to get it to work in taking my final table request and putting that into a hold file, not printing the site_id. Then I table filed that hold file, did the define for the site_id=if site_id_seq eq ' ' then site_id_class else site_id_seq. That output like I needed. I think in my second hold file where I was pulling the note nbr=' ' and seq=20 that was only pulling those so when I joined it to my first hold file there was no site_id_seq in my first hold file so it didn't have a value at all, not even blank. So I had to create another hold file to create the blank value and then everything worked. Does this even make sense? Completely different logic than mainframe focus.

Malinda

The more I think about this, logically, it kinda makes sense but it sure seems odd to me that you would have to put it all in a hold file just to generate the blank value. Just not what I am used to with mainframe focus I guess.

Unless you SET HOLDMISS = ON, WF will convert missing values from relational tables to blanks (for text values) and zeroes (for numeric vslues) in WF hold files.

So maybe instead of doing the extra hold file, before my final table request include the set holdmiss = on to see if that could make my define work?

malinda