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,

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

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

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!

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,
Can you give me an example of how the actual syntax would look?
Thanks!

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

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
...

Thank you all for your input!