All,

This is probably a simple issue. However, I have not worked with MATCH statements before. Basic idea, I have 2 tables (TTR and CTS). I want all of the specified records from the TTR table and any matching records from the CTS table. I thought LEFT_OUTER JOIN was suppose to achieve that. However, this code returns only entries with values in both of the TTR and CTS tables.

SET BYDISPLAY = ON
SET ASNAMES = ON
SET HOLDMISS = ON
-*********************************************************************
-* *
-*********************************************************************
-SET &RUNDATE = AYMD(&YYMD, -0,'I8YYMD');
-SET &RUNDATE1 = DATECVT((DATEADD((DATECVT (&RUNDATE.EVAL, 'I8YYMD', 'YYMD')),'D', -130)), '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', -2)), 'YYMD','I8YYMD');
-SET &RUNDATE3 = DATECVT((DATEADD((DATECVT (&RUNDATE.EVAL, 'I8YYMD', 'YYMD')),'D', -0)), 'YYMD','I8YYMD');
-*********************************************************************
-* *
-*********************************************************************
-SET &POSPATH = '/opt/' | &&ENVPATH | '/ibi/apps/fos/';
-*********************************************************************
-* *
-*********************************************************************
JOIN
LEFT_OUTER TTR.TTR.TTR_VIN_LAST8 AND TTR.TTR.TTR_VIN_FIRST9 IN TTR TO MULTIPLE
-* LEFT_OUTER TTR.TTR.TTR_VIN_LAST8 AND TTR.TTR.TTR_VIN_FIRST9 IN TTR TO UNIQUE
CTS.CTS.CTS_VIN_LAST8 AND CTS.CTS.CTS_VIN_FIRST9 IN CTS TAG J0 AS J0
END
-*********************************************************************
-* *
-*********************************************************************
TABLE FILE TTR
PRINT
TTR_REQ_NMBR
TTR_VIN_FIRST9
TTR_VIN_LAST8
TTR_REQ_DATE
TTR_AUC_DATE
TTR_REQ_COMMENT
TTR_TITLE_NMBR
TTR_MAIL_COMMENT
TTR_AUC_NMBR
CTS_CT_MAIL_DATE
CTS_TITLE_IND
CTS_CT_AUC_NMBR AS 'CTS_AUC_NMBR'
BY TTR_REQ_NMBR
WHERE (TTR_TYPE EQ 'D');
WHERE (TTR_REQ_COMMENT NE 'C');
WHERE (TTR.TTR.TTR_REQ_DATE GE &RUNDATE1 AND TTR.TTR.TTR_REQ_DATE LT &RUNDATE2);
WHERE (TTR.TTR.TTR_AUC_DATE LE &RUNDATE3);
WHERE (TTR_AUC_NMBR GT 0 AND TTR_AUC_NMBR NE 88888887);
WHERE (CTS_TITLE_IND NE 'S');
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE HOLD AS TTRF00A1 FORMAT ALPHA
-*AFTER MATCH HOLD AS TTRF00A1 FORMAT ALPHA OLD-OR-NEW
-*-*ON TABLE HOLD AS TTRF0001 FORMAT ALPHA
ON TABLE PCHOLD FORMAT EXL2K
END
-RUN
-EXIT

Thanks in advance for any suggestions.

Mitch

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

This sounds like you have two tables (TTR and CTS). and want to make one from them... Keeping it simple: F1 / Help ... MATCH FILE ... Merge Phrases... provides all your answers... maybe: AFTER MATCH HOLD OLD (OLD specifies that all records from the old data source, and any matching records from the new data source, are merged into the HOLD file.)

Hi Mitch,

The problem with your LEFT OUTER JOIN is that as soon as you put a condition on the secondary table (like WHERE (CTS_TITLE_IND NE 'S'); ) than it turns into an INNER JOIN.

Martin.

Martin is correct. The best way to do this is first do the join then Hold as a file, then do your where clause that is on the secondary file on the Hold file.

Mitch,

First question I have is what database are you using to store your information? Are these focus tables or an RDBMS like Oracle or DB2? FOCUS doesn't handle left outers well, especially not in 7.6. Its getting better in 8.0.08, although I'm still learning the nuances in the latest version. I am going to assume you are connecting to an RDBMS, so you will want to use the command

SET ALL = ON 
SET SHORTPATH=SQL 

SET ALL will turn all of your joins into left outer joins and SET SHORTPATH I believe will say to look at converting to SQL before considering other methods (Like WF managed joins etc). Also you probably want to run a SQL trace to validate what is actually being sent to your RDBMS.

Martin is also correct that a filter on your left outer table will convert the request into an inner join. Unfortunately, I don't know if 7.6 can handle filters in the join. I know in 7.7 you can do subqueries in the join statement. Another thing you might be able to get away with is to adjust your filter to be

 WHERE (CTS_TITLE_IND NE 'S') OR (CTS_TITLE_IND IS MISSING); 

. Although the issue with doing it that way is that the filter gets applied after the join instead of before.

Just throwing out thoughts here, but as long as you do not have a 1 to many situation you can always use defines instead of the filter (Irritating I know). Since you only have 3 fields from that table it might work if you do this:

 DEFINE FILE TTR
CTS_CT_MAIL_DATE_YYMD/YYMD WITH MISSING = IF CTS_TITLE_IND NE 'S' THEN CTS_CT_MAIL_DATE ELSE MISSING;
END