Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Getting all records from 1 table
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Getting all records from 1 table
 Login/Join
 
Member
posted
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>,


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 10 | Registered: August 13, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
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.)
 
Posts: 3106 | Location: Tennessee, Nashville area | Registered: February 23, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


WebFocus 8206M, iWay DataMigrator, Windows, DB2 Windows V10.5, MS SQL Server, Azure SQL, Hyperstage, ReportCaster
 
Posts: 161 | Registered: March 29, 2013Reply With QuoteReport This Post
Guru
posted Hide Post
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.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Reply With QuoteReport This Post
Master
posted Hide Post
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 


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Getting all records from 1 table

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.