Hi,

Can you rename your JOIN (maybe J1A instead of J0) and try again, and let me know if it works -

Regards,
Venkat

My original join looked like this:
JOIN IP WITH P_IP
IN LOG_DUMP
TO IP IN SITE_TO_IP
AS J0 END


The current join is:
JOIN
LOG_DUMP.LOG_DUMP.P_IP
IN LOG_DUMP
TO MULTIPLE SITE_TO_IP.SITE_TO_IP.IP
IN SITE_TO_IP TAG J0 AS J0
END

I do not get an error, but it does not join either. When I view the source it tells me number of records in table is 0.

Can you try this way?

JOIN
LOG_DUMP.LOG_DUMP.P_IP
IN LOG_DUMP
TO MULTIPLE SITE_TO_IP.SITE_TO_IP.IP
IN SITE_TO_IP TAG J1A AS J1A
END

Also replace all J0 with J1A, and see if it works. The reason I'm mentioning this, we had some issues with default J0 join in 7.4.
When it changed to a different name, it worked!

This message has been edited. Last edited by: Venkat-,

The original JOIN was a DEFINE-based join with IP as the DEFINEd field in LOG_DUMP. Do LOG_DUMP.P_IP and SITE_TO_IP.IP now have the same format and length so that they can be joined without the DEFINE? Also, it should be noted that you have changed the original one-to-one join to a one-to-many join. If it should be one-to-one, remove the MULTIPLE keyword from the JOIN statement.

Venkat - I did change the name of the JOIN, still does not work

Dan - My original is not a DEFINE-based JOIN, both fields have always been the same format and length. I also removed the MULTIPLE keyword, but still does not work.

quote:

JOIN IP WITH P_IP IN LOG_DUMP
TO IP IN SITE_TO_IP AS J0
END

I may be mistaken, but I believe the "WITH P_IP" clause in the above JOIN statement does indicate a DEFINE-based join.

quote:

Originally posted by DMA:

. . .
Here is my query:
TABLE FILE LOG_DUMP
SUM CNT.P_ACTION
BY SITE
WHERE P_IP CONTAINS IP;
WHERE P_ACTION EQ 'ACCEPT';
END

1. Why are you using 'CONTAINS', rather than 'EQ'?

2. Note that the JOIN already assures that
P_IP EQ IP;
so why include this test at all?

3. I wonder whether WF can pass through a condition of the form "where field1 contains field2;" in the generated SQL Select. If not, then the way the condition is imposed by WF might have changed, particularly if one or both fields are varchar.

quote:

Originally posted by j.gross:

quote:

Originally posted by DMA:

. . .
Here is my query:
TABLE FILE LOG_DUMP
SUM CNT.P_ACTION
BY SITE
WHERE P_IP CONTAINS IP;
WHERE P_ACTION EQ 'ACCEPT';
END

1. Why are you using 'CONTAINS', rather than 'EQ'?

2. Note that the JOIN already assures that
P_IP EQ IP;
so why include this test at all?

3. I wonder whether WF can pass through a condition of the form "where field1 contains field2;" in the generated SQL Select. If not, then the way the condition is imposed by WF might have changed, particularly if one or both fields are varchar.

I am using CONTAINS because the last octet of the IP in LOG_DUMP is not always the same.

Renming the join from J0 is a good idea. You could also try issuing a JOIN CLEAR right before the join to remove any existing joins that might have the same name.

Another thought - maybe the new version of Focus doesn't like your field names.... just a thought.

I've added JOIN CLEAR right before my JOIN and I've renamed the JOIN as well, neither seem to do the trick. If the new version does not like my field names, then what?

quote:

am using CONTAINS because the last octet of the IP in LOG_DUMP is not always the same.

Well if the last octet is not the same, and both P_IP and IP are 4-byte addresses, AND they are different, CONTAINS will not work.
So maybe, you want to take the first 3 octets of IP as the CONTAINS target.

Denise --

If "the last octet of the IP in LOG_DUMP is not always the same", maybe that's why the original code would have used a define, to mask the final byte.

Without some mechanism to confine attention to the first three, why would the join have found matches originally, and why expect it to do so now?

You also haven't explained why you switched from a one-one join to one-many ("MULTIPLE"). The one-one join would have generated a left outer join, which would return rows even when there is no match in site_to_ip. The one-many will generate an inner join.


What happens in you current code if you comment out the "WHERE" clauses?