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)Join Using Two Tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
(SOLVED)Join Using Two Tables
 Login/Join
 
DMA
Gold member
posted
Hello Everyone!

I need to join two tables (log_dump & site_to_ip). The fields I need to join are P_IP from log_dump and IP from site_to_ip. I don't have the last set of digits in site_to_ip. For example, in log_dump P_IP is 10.160.98.31 and in site_to_ip IP is 10.160.98.

Is there a way to do a join using "like"?

Thanks in advance for your input!

Denise

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


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Francis Mariani
Expert
posted Hide Post
I would do a "define-based" join, by defining a field in the host file that contains the first three sets of digits.

Described in the doc: Creating Reports With WebFOCUS Language > Joining Data Sources > Creating an Equijoin > Joining From a Virtual Field to a Real Field Using an Equijoin


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
Envision
Member
posted Hide Post
Along these lines....

Your define can be:
IP/A50=SUBSTR(50,P_IP,1,50-POSIT(REVERSE(50,P_IP,'A50'),50,'.',1,'I2'),50,'A50');
(stripping off everything after the last dot)

Then the join is:
JOIN IP WITH P_IP IN log_dump TO ALL IP IN site_to_ip AS J0


Release 7.6.6, Windows Server, HTML and Excel
 
Posts: 4 | Registered: June 02, 2008Report This Post
DMA
Gold member
posted Hide Post
Envision,

Thank you that did work, however, I forgot to mention that the IP with the missing digits represents a location and I need to display the IP with the correct location and it's not coming up that way.

Table log_dump has the full IP, table site_to_ip has the partial IP and the location name. When I run my report I need to see the location that corresponds to the correct, full IP.

Any suggestions?

Thanks!


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
You could of course have multiples.

Sum MIN. and MAX. of the full (four-element) IP address by the truncated (3-element) value. If Min = Max, you have a unique match; if not, not.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
DMA
Gold member
posted Hide Post
Jack,
Can you give me an example of how the actual syntax would look?
Thanks!


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Envision
Member
posted Hide Post
The following should display an P_IP from log_dump, the corresponding (truncated) IP from site_to_ip and a LOCATION (update this field name as appropriate) from site_to_ip. I have assumed P_IP is an A50, you should update the DEFINE of IP with a different length as appropriate. You can, of course, add whatever other fields are in either table:

JOIN IP WITH P_IP IN LOG_DUMP TO ALL IP IN SITE_TO_IP AS J0
DEFINE FILE LOG_DUMP
IP/A50=SUBSTR(50,P_IP,1,50-POSIT(REVERSE(50,P_IP,'A50'),50,'.',1,'I2'),50,'A50');
END
TABLE FILE LOG_DUMP
PRINT
P_IP
IP
LOCATION
END


Release 7.6.6, Windows Server, HTML and Excel
 
Posts: 4 | Registered: June 02, 2008Report This Post
j.gross
Virtuoso
posted Hide Post
With a define-based join as others posted,

JOIN ...
DEFINE ...
TABLE FILE LOG_DUMP
SUM
MIN.P_IP
MAX.P_IP
COMPUTE UNIQUE/I1=(MIN.P_IP EQ MAX.P_PI);
BY IP
...


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
DMA
Gold member
posted Hide Post
Thank you all for your input!


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report 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)Join Using Two Tables

Copyright © 1996-2020 Information Builders