Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Full Outer Join

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Full Outer Join
 Login/Join
 
BenC
Gold member
posted
Hi,

I am trying to run a Full Outer Join on to HOLD files. However, there are no options in Webfocus to do so in the GUI. So I was told to try the MATCH command. But when I used this command, the results seemed off sinec it would match the columns incorrectly.

I had the following code:
SET ALL = ON
-RUN
MATCH
FILE STEP1
SUM SHARES CAPITAL2
BY SYMBOL BY ORDER_ACTION
RUN
FILE STEP2
SUM FILL_AMOUNT CAPITAL
BY SYMBOL BY ORDER_ACTION
AFTER MATCH HOLD OLD-OR-NEW
END

The results seemed off because for a record that was in one table and not the other, it would skew the matching and throw off all the calculations.



WFSrv 7.64
WF Dev Studio 7.64

This message has been edited. Last edited by: Kerry,
 
Posts: 68 | Registered: March 15, 2006Report This Post
Spence
Platinum Member
posted Hide Post
if doing with a match i think you would want:
AFTER MATCH HOLD OLD
you would get all rows from STEP1 and the matching rows from STEP2


WF 8 version 8.2.04. Windows.
In focus since 1990.
 
Posts: 189 | Location: pgh pa | Registered: October 06, 2004Report This Post
Frans
Guru
posted Hide Post
Doesn't this work?

SET ALL - PASS
JOIN LEFT_OUTER X IN Y TO ALL X IN Z AS J1


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Darin Lee
Virtuoso
posted Hide Post
Frans - that would only get you a left outer join. That would be all the records in the left table along with any matches that were found in the right table. It looks like Ben is trying for a union more or less, which would be all the records from both tables, combining where possible the records that match on SYMBOL and ORDER_ACTION.

A match would get you what you need, with OLD-OR-NEW. Not sure why you're not getting what you need without seeing the master files and an example of the data and output.


Regards,

Darin


In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
BY SYMBOL BY ORDER_ACTION


What are the declarations for the two match-key fields in the two data sources? If the declarations are inconsistent, Focus will merge without regard to the key-field values.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Ted Michalski
Platinum Member
posted Hide Post
I have an issue with a LEFT_OUTER join. If I say:

JOIN
LEFT_OUTER QUERYTOT.QUERYTOT.JOINKEYTOT IN QUERYTOT TO QUERY2A.QUERY2A.JOINKEYA IN QUERY2A
AS J1
END

I don't get the rows I need. If I put in the "ALL"

JOIN
LEFT_OUTER QUERYTOT.QUERYTOT.JOINKEYTOT IN QUERYTOT TO ALL QUERY2A.QUERY2A.JOINKEYA IN QUERY2A
AS J1
END

I get anerror nessage stating that I have exceeded max messages:

Reporting Server messages exceeded IBIF_max_messages, report retrieval aborted.

Please, contact your WebFOCUS administrator


Any ideas?


7.7.02
Windows
EXCEL, PDF, CSV, TEXT
 
Posts: 106 | Registered: June 25, 2009Report This Post
Microfich
Gold member
posted Hide Post
Are your calcs skewed because of missing data? If so, prior to the MATCH you can:

SET NODATA=0


If you're adding SHARES to FILL_AMOUNT and CAPITAL to CAPITAL2, do so in a subsequent DEFINE. If not, are your numbers now good?

FYI, if you want a simple append of both files (not truly a full outer join) have you tried the MORE command? Only prerequisite is that all fields must be in the same format. In effect, the 2 hold files are appended. Resultant field names will be the names from the first file mentioned.

TABLE FILE STEP1
PRINT SYMBOL ORDER_ACTION SHARES CAPITAL2
ON TABLE HOLD AS STEP3
MORE
FILE STEP2
END
-RUN

(Note, this presumes SHARES and CAPITAL2 mean the same thing as FILL_AMOUNT and CAPITAL.)


WebFOCUS 8105
Windows;
DB2, UDB, SQL Server, Oracle
FOCUS-WebFOCUS since 1981
 
Posts: 84 | Registered: December 13, 2005Report This Post
csmathan
Member
posted Hide Post
quote:
get anerror nessage stating that I have exceeded max messages:


I believe your QUERY2A.QUERY2A.JOINKEYA has more than one record to match with QUERYTOT.QUERYTOT.JOINKEYTOT column. Webfocus will generate some warning messages saying that "DUPLICATES IN JOIN 'FROM' FIELD :" at the back end When your query returns more numbers of records and then the warning message also will get increasing for each duplicates. Started throwing this error when number of rows exceeds some limit

Use MATCH to get the same data to avoid the issue.


WebFOCUS 7.x
Windows, All Outputs
 
Posts: 19 | Registered: January 10, 2012Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Full Outer Join

Copyright © 1996-2020 Information Builders