This is a bit involved to explain. So, if I need to provide further clarification, please let me know. My company stores various vehicle documents at a third party location. When the documents for a specific vehicle need to be sent to a different location, we generate requests that are sent to the storage facility to gather/process/distribute those documents as requested. I have written a procedure that compares these requests for documents to where (if anywhere) those documents have been sent.

Here is a sample of the output of that report. "Hertz Auc #" and "Hertz Auc Name" correspond to who were in the requests for the documents to go to. "CT Auc #" and "CT Auc Name" show where the documents had been sent. In this case, "CT Auc #" of 105 is Adesa L.A. So, while all of these documents have been sent to Adesa L.A., only the first listing of that location actually displays the location. How can I modify the code to display the "CT Auc Name" for every row instead of just the first?

Request # Hertz Auc # Hertz Auc Name CT Auc # CT Auc Name
178273 1051010 THE HERTZ CORP 105 ADESA L.A.
176421 1051010 KANSAS CITY MO 105
174495 1051010 HERTZ ATLANTA RETAIL 105
177920 1051010 THE HERTZ CORPORATION 105
174497 1051010 THE HERTZ CORPORATION 105
182666 1051010 SIMPLY WHEELZ LLC D/B/A 105
172509 1051010 GREATER CHICAGO AUTO AUCT 105
176424 1051010 KANSAS CITY AUTO AUCTION 105


Procedure:

-*********************************************************************
-* Date SR#/TT# Pgmr Description of Modification *
-*---------- ----------- --------- ----------------------------------*
-* *
-*********************************************************************
-*-SET &ECHO = ALL;
-*********************************************************************
-* *
-*********************************************************************
-*SET BYDISPLAY = ON
-*SET ASNAMES = ON
-*SET HOLDMISS = ON
-SET ALL=ON;
-*********************************************************************
-* *
-*********************************************************************
-*-SET &MR_FULL_FEXNAME = 'MODELLIS';
-SET &MR_FULL_FEXNAME = 'H4JO5ZDC';
-*-SET &MR_FULL_FEXNAME = 'FOSR2B01';
-MRNOEDIT -INCLUDE NSTOPFOS
-*********************************************************************
-*SETTING THE DATE *
-*********************************************************************
-SET &RUNDATE = AYMD(&YYMD, -0,'I8YYMD');
-SET &RUNDATE1 = DATECVT((DATEADD((DATECVT (&RUNDATE.EVAL, 'I8YYMD', 'YYMD')),'D', -25)), 'YYMD','I8YYMD');
-*-SET &RUNDATE1 = DATECVT((DATEADD((DATECVT (&RUNDATE.EVAL, 'I8YYMD', 'YYMD')),'D', -1)), 'YYMD','I8YYMD');
-SET &RUNDATE2 = DATECVT((DATEADD((DATECVT (&RUNDATE.EVAL, 'I8YYMD', 'YYMD')),'D', -240)), 'YYMD','I8YYMD');
-*********************************************************************
-* *
-*********************************************************************
-SET &POSPATH = '/opt/' | &&ENVPATH | '/ibi/apps/fos/';
-*********************************************************************
TABLE FILE TTR
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
-* TTR_TITLE_NMBR
BY TTR_VIN_LAST8 NOPRINT
BY TTR_VIN_FIRST9 NOPRINT
BY HIGHEST 1 TTR_REQ_DATE NOPRINT
WHERE (TTR.TTR.TTR_REQ_DATE GE &RUNDATE1);
WHERE (TTR_TYPE EQ 'W');
WHERE (TTR_AUC_NMBR GT 1051000);
WHERE (TTR_AUC_NMBR NE 88888887);
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TTRR00A1 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-*-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE CTS
PRINT
CTS_VIN_FIRST9
CTS_VIN_LAST8
CTS_REQ_DATE
CTS_CT_MAIL_DATE
CTS_TITLE_IND
-* CTS_CT_AUC_NMBR AS 'CTS_AUC_NMBR'
CTS_CT_AUC_NMBR
-*BY CTS_VIN_LAST8 NOPRINT
WHERE CTS_TITLE_IND NE 'S' AND CTS.CTS.CTS_REQ_DATE GE &RUNDATE1;
-*WHERE (CTS.CTS.CTS_REQ_DATE GE &RUNDATE1);
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CTSR00A0 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-*-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE CTSR00A0
PRINT
CTS_VIN_FIRST9
CTS_VIN_LAST8
CTS_REQ_DATE
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-* CTS_AUC_NMBR
BY CTS_VIN_LAST8 NOPRINT
BY CTS_VIN_FIRST9 NOPRINT
BY HIGHEST 1 CTS_REQ_DATE NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CTSR00A1 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-*-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
JOIN
LEFT_OUTER TTRR00A1.TTRR00A1.TTR_VIN_LAST8
AND TTRR00A1.TTRR00A1.TTR_VIN_FIRST9 IN TTRR00A1 TO MULTIPLE
CTSR00A1.CTSR00A1.CTS_VIN_LAST8
AND CTSR00A1.CTSR00A1.CTS_VIN_FIRST9 IN CTSR00A1 TAG J2 AS J2
END
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE TTRR00A1
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
-* TTR_TITLE_NMBR
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-* CTS_AUC_NMBR
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TTRF00B1 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE TTRC
PRINT
TTRC_VIN_FIRST9
TTRC_VIN_LAST8
TTRC_COMMENT
BY TTRC_VIN_LAST8 NOPRINT
BY TTRC_VIN_FIRST9 NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TTRCF001 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
JOIN
LEFT_OUTER TTRF00B1.TTRF00B1.TTR_VIN_LAST8
AND TTRF00B1.TTRF00B1.TTR_VIN_FIRST9 IN TTRF00B1 TO MULTIPLE
TTRCF001.TTRCF001.TTRC_VIN_LAST8
AND TTRCF001.TTRCF001.TTRC_VIN_FIRST9 IN TTRCF001 TAG J1 AS J1
END
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE TTRF00B1
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
TTRC_COMMENT
-* TTR_TITLE_NMBR
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-* CTS_AUC_NMBR
BY TTR_AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS TTRF00C1 FORMAT ALPHA
-*ON TABLE PCHOLD AS TTRF00C1 FORMAT WP
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE AUC
PRINT
AUC_NAME
AUC_NMBR
-*BY AUC_NMBR AS 'TTR_AUC_NMBR'
BY AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS ACUF00A1 FORMAT ALPHA
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE CTA
PRINT
CTA_CT_AUC_NAME
CTA_CT_AUC_NMBR
-*BY CTA_CT_AUC_NMBR AS 'CTA_AUC_NMBR'
BY CTA_CT_AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CTAF00A1 FORMAT ALPHA
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE CTAF00A1
PRINT
CTA_CT_AUC_NAME
CTA_CT_AUC_NMBR
-*BY CTA_AUC_NMBR
BY CTA_CT_AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS CTAF00B2 FORMAT ALPHA
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
MATCH FILE ACUF00A1
SUM
AUC_NAME
-*BY TTR_AUC_NMBR
BY AUC_NMBR
RUN
FILE TTRF00C1
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
TTRC_COMMENT
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-*CTS_AUC_NMBR
BY TTR_AUC_NMBR NOPRINT
-*AFTER MATCH HOLD AS ACUF00A2 OLD-AND-NEW
AFTER MATCH HOLD AS ACUF00A2 OLD-OR-NEW
-*AFTER MATCH HOLD AS ACUF00A2 OLD
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE ACUF00A2
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
TTRC_COMMENT
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
AUC_NAME
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-*CTS_AUC_NMBR
-*BY TTR_AUC_NMBR
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS ACUF00A2 FORMAT ALPHA
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE ACUF00A2
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_REQ_COMMENT
TTRC_COMMENT
TTR_MAIL_COMMENT
TTR_AUC_DATE
TTR_AUC_NMBR
AUC_NAME
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR
-*BY CTS_AUC_NMBR
BY CTS_CT_AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
ON TABLE HOLD AS ACUF00A4 FORMAT ALPHA
-*ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT
-*********************************************************************
-* *
-*********************************************************************
JOIN CLEAR *
JOIN
LEFT_OUTER ACUF00A4.CTS_CT_AUC_NMBR IN ACUF00A4 TO MULTIPLE
CTAF00B2.CTA_CT_AUC_NMBR IN CTAF00B2 TAG J0 AS J0
END
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE ACUF00A4
PRINT
TTR_REQ_NMBR AS 'Request #'
TTR_VIN_FIRST9 AS 'VIN First 9'
TTR_VIN_LAST8 AS 'VIN Last 8'
TTR_REQ_DATE AS 'Req Date'
TTR_REQ_COMMENT AS 'Req Comment'
-*TTR_TITLE_NMBR AS 'Follow Up Comment'
TTRC_COMMENT AS 'Follow Up Comment'
TTR_MAIL_COMMENT AS 'Mail Comment/FedEx #'
-*TTR_AUC_DATE AS 'Follow-Up Date'
TTR_AUC_DATE AS 'Follow-Up Date'
TTR_AUC_NMBR AS 'Hertz Auc #'
AUC_NAME AS 'Hertz Auc Name'
CTS_CT_AUC_NMBR AS 'CT Auc #'
CTA_CT_AUC_NAME AS 'CT Auc Name'
CTS_TITLE_IND AS 'CT Stat Ind'
-*CTS_CT_MAIL_DATE AS 'CT Mail Date'
-*BY TTR_AUC_NMBR NOPRINT
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE HOLD AS ACUF0005 FORMAT ALPHA
-*ON TABLE PCHOLD AS XXXXXXXX FORMAT WP
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-*-EXIT


Thanks,
Mitch

This message has been edited. Last edited by: <Kathryn Henning>,

quote:

-*SET BYDISPLAY = ON

Cahnge the above to

SET BYDISPLAY = ON

I made that change. However, it still did not display repeating names after the first listing.

Mitch,

without sifting through your copyrighted code ( you'll probably will be fired for sharing this ).

Did you try to dump the data?
e.g.

TABLE FILE ACUF00A4
PRINT *
END

Which you basically do already.

You have no BY fields. Hence, the problem isn't webfocus failing to show repeated values...
...it's the fact that these fields are actually empty.

I guess something is wrong with the source or joins.

Good luck,
Dave

Mitch,

The only way that I can think of right now is by using a define such as the following
DEFINE PRINT_NAME/A40=IF CT_AUC_NAME EQ LAST CT_AUC_NAME THEN '' ELSE CT_AUC_NAME;
END

Then instead of printing CT_AUC_NAME print
PRINT_NAME
<\

I think the issue has to do with the 2 table that are being joined together (ACUF00A4 and CTAF00B2). Query ACUF00A4 before the join and make sure you have only 1 instance of CTS_CT_AUC_NMBR. If you have multiple you might try changing the hold format and adding an index on the 2 table to

 
ON TABLE HOLD AS ACUF00A4 FORMAT FOCUS INDEX CTS_CT_AUC_NMBR 
and 
ON TABLE HOLD AS CTAF00B2 FORMAT FOCUS INDEX CTA_CT_AUC_NMBR