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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Joining same table twice

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Joining same table twice
 Login/Join
 
Gold member
posted
Hi All,

Can I join the same table twice to join to different fields

ex:

Table A
NAME | DATE1 |

Table B
NAME | DATE2

Table C
DATE

Can I do a join like,

JOIN NAME IN TABLE A TO UNIQUE NAME IN TABLE B AS J0 END
JOIN DATE1 IN TABLE A TO UNIQUE DATE IN TABLE C AS J1 END
JOIN DATE2 IN TABLE A TO UNIQUE DATE IN TABLE C AS J2 END


Thanks,

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


In Focus since 2008
WebFOCUS 8.2.0.1
Windows 7 - IE,Chrome,Firefox
Excel, PDF, HTML, AHTML, XML
JavaScript, jQuery, D3.js, Highcharts
 
Posts: 79 | Location: New York | Registered: February 04, 2010Report This Post
Expert
posted Hide Post
Yes you can, but you might want to use the TAG option within your joins to help identify the columns in the two TABLE C.

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
Expert
posted Hide Post
Yes, but you will have to reference the columns in Table C with a prefix.

e.g.
JOIN NAME IN TABLE A TO UNIQUE NAME IN TABLE B AS J0
JOIN DATE1 IN TABLE A TO UNIQUE DATE IN TABLE C TAG T1 AS J1
JOIN DATE2 IN TABLE A TO UNIQUE DATE IN TABLE C TAG T2 AS J2

Also you need to be careful of the sort paths.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
Recursive Join possible only for FOCUS or IMS data source. Either specify a unique Join Name or Use Tag names.

Here is a sample code

DEFINE FILE CAR
COUNTRY_N/A16=COUNTRY;
CAR_N/A16=IF CAR EQ 'AUDI' OR 'JENSEN' THEN 'ENGLAND' ELSE CAR;
END
TABLE FILE CAR
SUM
RETAIL_COST
BY COUNTRY_N
BY CAR_N
ON TABLE HOLD AS HLD_1 FORMAT FOCUS
END
DEFINE FILE CAR
COUNTRY_N/A16=COUNTRY;
END
TABLE FILE CAR
SUM
DEALER_COST
BY COUNTRY_N
BY CAR
ON TABLE HOLD AS HLD_2 FORMAT FOCUS INDEX COUNTRY_N
END

JOIN COUNTRY_N IN HLD_1 TO COUNTRY_N IN HLD_2 AS J1
JOIN CAR_N IN HLD_1 TO COUNTRY_N IN HLD_2 AS J2
-RUN

?FF HLD_1

TABLE FILE HLD_1
PRINT *
END
 
Posts: 542 | Location: Dearborn, MI | Registered: June 03, 2009Report This Post
Master
posted Hide Post
quote:
Recursive Join possible only for FOCUS or IMS data source.


Not True.

I do recursive joins with Oracle all the time. For example:

JOIN CL_CLSSN IN KDSCLINT TAG A TO CL_CLSSN IN KDSCLINT TAG B
TABLEF FILE KDSCLINT
 PRINT B.CL_CLID
    BY A.CL_CLID
 WHERE A.CL_CLID NE B.CL_CLID
    ON TABLE HOLD
END

generates this SQL

SELECT T1."CL_ID",
       T1."CL_SSN_NBR",
       T2."CL_ID"
  FROM KIDSPROD.TCLIENT T1,
       KIDSPROD.TCLIENT T2
 WHERE (T2."CL_SSN_NBR" = T1."CL_SSN_NBR")
   AND (T1."CL_ID" <> T2."CL_ID")
 ORDER BY T1."CL_ID";


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
Expert
posted Hide Post
"Can I join the same table twice to join to different fields" is not a recursive join and Tony answered the question.


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
Expert
posted Hide Post
quote:
Tony answered the question


Only by seconds....
Good One Tony


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
quote:
Only by seconds....

... and from the opposite side of the world, how close was that!

I reckon that it was milliseconds myself because your post came up during the auto refresh upon submission, so we must have hit the button almost simultaneously.

Just goes to show ..... something, not sure what, but it definitely shows something 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
Expert
posted Hide Post
... and I think we all missed Warren's move into Virtuoso level (well, I did anyway Frowner) - well done Warren!

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
I was exiting getting close, but a non event getting there.

No Fireworks or marching bands.

It was a bit like turning 40. Big Grin


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Congrats Warren!

I'm four (now three!) away from 5000 - I bet it will feel like turning 50!


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
Virtuoso
posted Hide Post
Well, I am getting jealous now since I still have a looooooong way to go.

I may not have too much to celebrate by having been recently "promoted" from Gold Member to Platinum Member except perhaps for the fact that I no longer have the weird sensation of being an Austin Powers character Music

Good one to Warren ... and Francis, you're almost there!

- Neftali.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
Well, we will have to make sure that you're congratulated.

You are a pioneer, forging into unknown territory.


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: 6349 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
Francis, acutally your total posts is way over 5000. Check the member directory! Way to go! Big Grin


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
Expert
posted Hide Post
And I had a different name in a previous life...


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
Expert
posted Hide Post
quote:
a different name in a previous life..
Plus another on an earlier board if I remember correctly.

Neftali, keep goin baby!

T
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Expert
posted Hide Post
Porkpie Hat, anyone?


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
Virtuoso
posted Hide Post
If I dare get back on topic, consider making two passes at that table, both for the sanity of you and your future maintenance coders, and to establish better control on any multiplicative effects that could occur. Were it me I would take one pass at it for the first join, prefix every field while doing it, and then use the resulting hold file with a single join a second time.

You CAN do it with the double join, just be careful. It would be oh-so-easy to have the wrong field in a where clause.

J.

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



 
Posts: 1012 | Location: At the Mast | Registered: May 17, 2007Report 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] Joining same table twice

Copyright © 1996-2020 Information Builders