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] Battling to understand different join results

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Battling to understand different join results
 Login/Join
 
Jinx
Gold member
posted
Hi,

I have searched the forum and understand that supposedly, the following is correct:

Inner Join:
SET ALL = OFF


Left Outer Join:
SET ALL = ON


BUT no matter what I try I get LEFT JOIN in both cases.

So I am trying with some sample data as follows

TABLE1
T1CODE T1DESC
1 ITEM_1_T1
2 ITEM_2_T1
3 ITEM_3_T1

TABLE2
T2CODE T2DESC
3 ITEM_3_T2
4 ITEM_4_T2
5 ITEM_5_T2
9 ITEM_9_T2

EXAMPLE 1:

SET ALL = OFF
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END


PRODUCES:

T1CODE T1DESC T2CODE T2DESC
1 ITEM_1_T1 . .
2 ITEM_2_T1 . .
3 ITEM_3_T1 3 ITEM_3_T2

EXAMPLE 2:


SET ALL = ON
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END


PRODUCES:

T1CODE T1DESC T2CODE T2DESC
1 ITEM_1_T1 . .
2 ITEM_2_T1 . .
3 ITEM_3_T1 3 ITEM_3_T2

WEIRD EXAMPLE 3 (WITH ALL = OFF AND AN ALL IN THE JOIN:

SET ALL = OFF
JOIN CLEAR *
JOIN T1CODE IN TABLE1 TO ALL T2CODE IN TABLE2 AS J1
END

TABLE FILE TABLE1
PRINT
T1CODE
T1DESC
T2CODE
T2DESC
END


PRODUCES:

T1CODE T1DESC T2CODE T2DESC
3 ITEM_3_T1 3 ITEM_3_T2


I am totally confused, because surely things should not work this way...from what I have read on this forum it seems very odd.

Maybe someone can explain why this may be?

Thanks!

Jinx.

This message has been edited. Last edited by: Jinx,


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Sayed
Guru
posted Hide Post
Not sure if this is what you're trying to do



For left outer join

JOIN
LEFT_OUTER F0006.F0006.ADDRESS_NUMBER IN F0006 TO MULTIPLE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J0 AS J0
END


For inner join

JOIN
INNER F0006.F0006.ADDRESS_NUMBER IN F0006 TO MULTIPLE
F0101.F0101.ADDRESS_NUMBER IN F0101 TAG J1 AS J1
END


Sayed


WF 8.x and 7.7.x Win/UNIX/AS400, MRE/Portal/Self-Service, IIS/Tomcat, WebSphere, IWA, Realmdriver, Active Directory, Oracle, SQLServer, DB2, MySQL, JD Edwards, E-BIZ, SAP BW, R/3, ECC, ESSBASE
 
Posts: 285 | Location: Texas | Registered: June 27, 2006Report This Post
Jinx
Gold member
posted Hide Post
Hi!

Thanks for the reply.

I have used the LEFT_OUTER before, but the issue here is that I am trying to rewrite a complicated report written in FOCUS so that it can be used locally in webFOCUS. This report uses tons of complex joining, I had assumed joining worked a certain way, but cannot seem to replicate the results expected for the report - so needed some clarification on where my logic is faling on how the standard join syntax works.

I didn't know one could specify 'INNER' in the join so did learn something. ;-)

Thanks anyway.

Jinx.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
<AdeshRai>
posted
Hello Jinx,
i am assuming that u r working with focus files. in wf Left outer, Inner or other joins doesnt exist. these type of joins exists in RDBMS systems like oracle etc....
when you are trying to create an inner join give all option e.g.
join abc in xyz to all abc in def as jo1
end
else you can write the same thing what you have give as eg and put a where condition where the fields of cross reference table is not blank.
if you are creating a unique join then it will act as left outer even if you set all parameter = off. sometimes this problem occurs if you are trying to join any RDBMS table to a focus table(.foc).
when i have to create a left outer i use SET ALL=PASS and that works for me.
hope this helps.
 
Report This Post
Francis Mariani
Expert
posted Hide Post
We should assume you're working with SQL Server 2000 or 2008 tables.

Did you turn SQL tracing on to see the SQL generated by WebFOCUS? If you turn SQL tracing on, you will get some very informational messages about the JOINs...

SQL Traces


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
Jinx
Gold member
posted Hide Post
O.o I am as thick as a brick, it never occurred to me to do that! Good point!

Thanks Francis for point out the obvious solution to my woes Razzer.


7.6.11
Windows
HTML, PDF, Excel etc
DevStudio/Webfocus/Focus IBM
SQL Server 2000 / 2008
DB2
 
Posts: 78 | Location: UK | Registered: February 07, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
I have the SQL trace commands in a fex that I include an ALL my programs. As well, when I start writing a new report, I always SET XRETRIEVAL=OFF so no data is retrieved and the trace is immediately displayed. This makes development a lot more efficient and no amount of pointing-and-clicking in a GUI will replace these two basic ideas.

Cheers,


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
  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] Battling to understand different join results

Copyright © 1996-2020 Information Builders