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] Joining multiple datatabase tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Joining multiple datatabase tables
 Login/Join
 
Shankar
Guru
posted
Hi,
I have 3 DB tables A,B,C.A and B is having a same column and B and C is having a same column.How to Join these 3 tables?

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


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Danny-SRL
Virtuoso
posted Hide Post
JOIN COLAB IN A TO COLAB IN B AS J1
JOIN COLBC IN A TO COLBC IN C AS J2


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Shankar
Guru
posted Hide Post
Danny,
That is ok.Actually,I am trying to JOIN 3 files ACT,GAN and SUM.The code which I am using is as:

JOIN ACT.REF_NUM IN ACT TO
GAN.CP_NBR IN GAN AS JN1
END
JOIN SUM.MOVE_NBR IN SUM TO
ACT.MOVE_NBR IN ACT AS JN2
END

When I am displaying the columns of ACT,it is showing me the columns of ACT and GAN.But when I am displaying the columns of SUM,it is showing the columns of SUM and ACT only.Ideally,it should show me the columns of all the 3 files.
Can anyone suggest what can be the problem?


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
GinnyJakes
Expert
posted Hide Post
You have to do it the way Danny said.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
mstanislov
Member
posted Hide Post
It is painfully obvious that you're a beginner, you've not done your homework or even tried to search this out. Next time, instead of asking someone to do your work for you, ask for how to search out the answer.

Go to IBI.COM and search their technical documentation library. If you want to do this in Developer Studio, enter WebFocus as the product. If you want to code it yourself, as it appears you do, enter FOCUS as the product. Don't worry about the release. This has not changed in years. In the SEARCH FOR box, enter JOIN.

And ask for training.


WebFocus 7.6
Windows
outputs vary: have used HTML, PDF and Excel
 
Posts: 5 | Registered: September 19, 2008Report This Post
Shankar
Guru
posted Hide Post
Hi mstanislov,
I understand what you said.I asked the question after doing my homework.But,the problem which i am facing is quite unusual.When I am trying to display the columns of ACT after JOINing it with GAN (using ? HOLD ACT),it is showing me the columns of both the tables ACT and GAN.Now,I joined this resultant table with SUM.So,it should also show me the columns of all the 3 tables ACT,GAN and SUM when i do ? HOLD SUM.But,it is only showing me the columns of SUM and actual columns of ACT only,not the original columns of GAN.
How is this possible? Any suggestion.

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Shankar
Guru
posted Hide Post
Can anyone reply plzz.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Tom Flynn
Expert
posted Hide Post
Shankar,

This is programming AND WebFOCUS 101:
  
JOIN ACT.REF_NUM  IN ACT TO GAN.CP_NBR IN GAN AS JN1
JOIN SUM.MOVE_NBR IN SUM TO ACT.MOVE_NBR IN ACT AS JN2

is illogical, the HOST file is ACT, not SUM:
JOIN ACT.REF_NUM  IN ACT TO GAN.CP_NBR   IN GAN AS JN1
JOIN ACT.MOVE_NBR IN ACT TO SUM.MOVE_NBR IN SUM AS JN2

Just as Danny gave you above.

The GUI only does what the "programmer" tells it to do, it's not magic...

Tom


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
Shankar
Guru
posted Hide Post
Hi Tom,
Can't i use SUM as host file and ACT as reference file for second join which will be further used for reporting?

Thanks.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Tony A
Expert
posted Hide Post
You get told how to do it by Danny, advice you seem to ignore or not follow.

Ginny points you back at Danny's post intimating that you re-read it. You obviously didn't.

Tom spells it out and I bet you still don't get it.

Another one to add to your ignore lists peeps Confused

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
GinnyJakes
Expert
posted Hide Post
One last shot.

Shankar, if you do the joins as Danny and Tom have spelled out for you, once join1 is done, the MOVE_NBR in file B is logically part of file A. In order to bring file C into the join, the host of the 2nd join is logically file A.

Do the join as specified by Danny and Tom and then do a CHECK FILE ACT PICTURE. You will see the data as you want it. Then do your report and I'm sure that the answer will be correct.

There are 4 people telling you the correct way to do it. I think the odds are in our favor. Just give it a try.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Waz
Expert
posted Hide Post
Shankar, keep in mind that to join 3 tables together, you join the first two, then effectively add the third to the first.

The same goes with the forth, fifth, etc.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
M Cavanaugh
Member
posted Hide Post
S,
If you are having problems understanding the JOIN structure - a picture might help.

Use the CHECK FILE command to see the logical file structure of your JOIN'd file.


WebFOCUS Production: UNIX 7.7.03M
WebFOCUS Test: UNIX EDASERVE 7.7.05 Client 8.0.01
 
Posts: 28 | Location: Connecticut USA | Registered: August 29, 2006Report This Post
Frans
Guru
posted Hide Post
a basic course sql would also help Smiler

your joining from 2 different parent files and will therefore never get the data in one request.


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
GinnyJakes
Expert
posted Hide Post
Frans, based on the criteria in the first post this join can be done the way Danny and Tom said in subsequent posts. You join A to B. Then you join A to C with a field from B. You then get A ==> B ==> C.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Frans
Guru
posted Hide Post
Ginny, I was pointing to Shankars 2nd post. he does this ? HOLD ACT with correct results and ? HOLD SUM with only SUM fields.

Of course it can be done by joining a to b to c... but only your parentfile will contain the joined fields. That's why I suggested a basic course in SQL Smiler


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
GinnyJakes
Expert
posted Hide Post
Unless I misunderstood, he is trying to join all 3 together and Danny's solution is the answer.

But the point is moot. Shankar seems to have left the building.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Shankar
Guru
posted Hide Post
I got the idea.Thank you all.


WF 8.1.04,Windows 7,
DataBase: Oracle 11g,Output :Excel,PDF,HTML
 
Posts: 281 | Location: India | Registered: April 21, 2007Report This Post
Tony A
Expert
posted Hide Post
He could be on that basic course Wink ....



but I doubt it. Not all the time someone is prepared to give him the answer!!

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
Hua
Guru
posted Hide Post
Missing the columns in the 3rd file usually means the syntax of the join is incorrect.
-SET &ECHO=ALL
will show you what is wrong with the join. If you couldn't figure it out, the people here can quickly point out what is wrong if you post the executed codes/error messages.

Hope this will help.

Hua


Developer Studio 7.6.11
AS400 - V5R4
HTML,PDF,XLS
 
Posts: 305 | Location: Winnipeg,MB | Registered: May 12, 2008Report This Post
EricH
Platinum Member
posted Hide Post
I can see where the JOIN syntax could be confusing from someone coming from the SQL world.

In the SQL world you would always join A.COLAB to B.COLAB, then B.COLBC to C.COLBC (and C.COLCD to D.COLCD, etc). This is what Shankar was trying to do

As various folks have noted, in FOCUS, the 'from' side is always A.

EricH
 
Posts: 164 | Registered: March 26, 2003Report 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] Joining multiple datatabase tables

Copyright © 1996-2020 Information Builders