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     [CLOSED] Outer Join on only one of the Joins

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Outer Join on only one of the Joins
 Login/Join
 
Francis Mariani
Expert
posted
I have the following Join. I would like an outer join on only the last one, that is, I would like to retrieve the data from the first four inner joins and retrieve data from the last join whether or not there is data in the fifth join. Does this make any sense?

JOIN
BASEL_BSL_CL_MG_ACT_D.SP_ACT_KEY IN BASEL_BSL_CL_MG_ACT_D TO ALL
BASEL_BSL_CL_MG_ACT_F.SP_ACT_KEY IN BASEL_BSL_CL_MG_ACT_F AS J1
END
JOIN
BASEL_BSL_CL_MG_ACT_D.OWN_RESP_NODE_ID IN BASEL_BSL_CL_MG_ACT_D TO
BASEL_BSL_REPTG_RESP_F.RESP_NODE_ID IN BASEL_BSL_REPTG_RESP_F AS J2
END
JOIN
BASEL_BSL_REPTG_RESP_F.CNTRY_PROV_STAT_ID IN BASEL_BSL_CL_MG_ACT_D TO
BASEL_PROVINCE_STAT_D.CNTRY_PROV_STAT_ID IN BASEL_PROVINCE_STAT_D TAG J3 AS J3
END
JOIN
BASEL_BSL_CL_MG_ACT_D.CNTRY_PROV_STAT_ID IN BASEL_BSL_CL_MG_ACT_D TO
BASEL_PROVINCE_STAT_D.CNTRY_PROV_STAT_ID IN BASEL_PROVINCE_STAT_D TAG J4 AS J4
END
JOIN
BASEL_BSL_CL_MG_ACT_D.APPL_DIM_KEY IN BASEL_BSL_CL_MG_ACT_D TO
BASEL_BSL_CL_APPL_FORM.APPL_DIM_KEY IN BASEL_BSL_CL_APPL_FORM AS J5
END

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Roland
Gold member
posted Hide Post
Hi Francis,

I think yes it makes sense. And if you are using WF 71 or higher then you are even (maybe) lucky. Have a look at the documentation. Where I found it actually was in 'New Features V7.1'. I guess it's also mentioned in the regular docs.

Basically you may mention LEFT_OUTER as a key word in the JOIN command. There are some limitations, like it is only possible between tables of the same relational engine. But hopefully it helps you


Roland

Prod: WF 7.1.5
Test: WF 7.6.4
Unix Sun Solaris
HTML, PDF, EXL2K
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Report This Post
Roland
Gold member
posted Hide Post
Francis,

Sorry, but in the e-mail digest I could not see you are on WF 5xx. So my suggestion would not help you.

This seems to be sort of a drawback in the e-mail digest. The signature does not appear! Therefore I did not see your releases you are working on. Again sorry!


Roland

Prod: WF 7.1.5
Test: WF 7.6.4
Unix Sun Solaris
HTML, PDF, EXL2K
 
Posts: 54 | Location: Switzerland | Registered: May 13, 2003Report This Post
Leah
Virtuoso
posted Hide Post
Sometimes using the
SET ALL = ON

in 5.3 will work, but sometimes it doesn't in our DB2 world.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Roland, thanks for trying!

Leah, SET ALL=ON will affect ALL the JOINs, I want to only have one LEFT OUTER JOIN.

Thanks.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tom Flynn
Expert
posted Hide Post
Francis,

I'm sure you've already thought of this: tabling out the first 4 into a hold, then doing the outer join on the hold.

Just a thought....


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Tom, unfortunately I'm dealing with 418,000 records!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
Francis

How many records does the last table have?
and how many fields does it have?

If it's not to much you might consider solve this with a decode function.

And other way could be let the joins be done on the server with a sql (if it is sql)




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Jim_at_LM
Gold member
posted Hide Post
Francis:

As I see it, a TABLE inversion on BASEL_BSL_CL_APPL_FORM is what your trying to achieve. Based on data in ...APPL_FORM table, you want to control retrieval from the other 4 JOINs. I used to do these with hierarchies, and it worked well. I can't recall a recent relational JOIN structure like this and doing a query, but see if an inversion will work i.e.:
TABLE FILE mfdname.BASEL_BSL_CL_APPL_FORM (hierarchy method) - in relational sense, JOIN 5 should be first to ...MG_ACT_D. Then leave the other 4 JOINs the way you have them.


WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL
 
Posts: 77 | Location: Baltimore | Registered: May 31, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
Not all the rows retrieved in the first four JOINs will have values in the fifth JOIN. I would like the final output to include all the rows, even those that do not have values in the fifth JOIN. I wonder if MATCH would work - do the first four JOINs and then MATCH that structure to the fifth TABLE? I usually steer clear from MATCH and DBMS tables.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Darin Lee
Virtuoso
posted Hide Post
This may not be valid, but you might try defining the last join as a static join in an alternate MFD of BASEL_BSL_CL_MG_ACT_D with a segtype=KU.

We usually handle this sort of thing with a view in the DBMS i.e. create a view of that table in the DBMS that is linked and defined as a left outer join. When you generate the MFD for that view, it just appears as a field and you don't even have to worry about the join because the DBMS manages that by itself.


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
Jessica Bottone
Platinum Member
posted Hide Post
You mentioned that these are are DBMS tables but are they RDBMS tables like DB2, Oracle, etc? If they are, have you considered doing SQL pass-thru? That will give you the capability to do exactly what you want.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
 
Posts: 126 | Registered: January 18, 2007Report This Post
Francis Mariani
Expert
posted Hide Post
Jessica, they're DB2 tables, so yes, I could use SQL pass-thru. Thank you for the suggestion. I'm not as well versed with SQL as with FOCUS so I hesitated going this route - though it sounds like the best one.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Leah
Virtuoso
posted Hide Post
I have found that straight focus against a left outer join 'goes out to lunch'. The passthru may be the answer. Another option is pull from all but the one that may or may not have data, then pull from the table which you are using the outer join for the information and use hold file processing or match which ever is more to the solution.


Leah
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
Apparently, in v7.7 a new setting is available:

SET ALL=SQL


Though I just don't understand why "a setting... can be added to your procedure so the left outer join stays a left outer join".


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
I know what you mean Francis, when I code a left outer join, it means that is what I want.

Ok, we all know about optimisation and how the language is taking a more "gentle on the end user" approach, thus allowing complete novices to "code" a WebFOCUS procedure, but I still think that if folks want to do more than just the very basics then they owe it to themselves to learn a little about what they are really doing.

Let's face it, you wouldn't let your 16 year old drive your ferrari without knowing how to drive would you? (USA excluded Wink)

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Francis Mariani
Expert
posted Hide Post
From the case I opened:

quote:
The issue is the combination of a left-outer join and a WHERE statement that references a field in the cross-referenced file from the join (the second file). When doing a left-outer join, there may be records that have missing or empty values for the fields from the cross-ref file. Because the WHERE statement is using a field in the cross-ref file, it automatically assumes the data must be there for it to process, so it assumes the data cannot be missing or empty. To guaruntee this, it performs as an inner join instead - with an inner join, the data will always be there.

It is not correct doing this behavior, which is why the option SET ALL=SQL was added to release 7.7.


Isn't this what the "OUTER" in LEFT OUTER JOIN is all about?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Tony A
Expert
posted Hide Post
quote:
it automatically assumes

The "IT" in this phrase means the language.

The language is written by a human which would therefore mean that the human decided that they know better than the end programmer?

From the help file on the SET ALL command -

quote:
Note: A request with WHERE or IF criteria, which screen fields in a segment that has missing instances, omits instances in the parent segment even if you use the SET ALL=ON command. To include these instances in the report, use the SET ALL=PASS command.


Interesting last sentance!

This is why I have always advocated the use of SQL passtrhu when doing anything other than plain vanilla requests. It gives you more control over your results than the interpretted language does, everytime.

I suppose the important word here is "interpretted". The language syntax has to "interpret" what the majority of instances actually mean and in this case I would have thought that OUTER JOIN should mean just that?

Over to you Francis Wink

T


In FOCUS
since 1986		WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report 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     [CLOSED] Outer Join on only one of the Joins

Copyright © 1996-2020 Information Builders