Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (SOLVED)Join Using Two Tables
Go
New
Search
Notify
Tools
Reply
  
(SOLVED)Join Using Two Tables
 Login/Join
 
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, 2008Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2005Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     (SOLVED)Join Using Two Tables

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.