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.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] JOIN
 Login/Join
 
DMA
Gold member
posted
I need to join two tables. The first table has a field called P_IP and the second one is IP. The join worked just fine in WF version 7.6.8. After modifying the syntax for WF version 7.7.4, it does not work.

Here is my JOIN:
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

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

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


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Prarie
Virtuoso
posted Hide Post
what did you modify and why? When you say it does not work...does not join or error?


In Focus since 1993. WebFOCUS 7.7.03 Win 2003
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Venkat-
Silver Member
posted Hide Post
Hi,

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

Regards,
Venkat


product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF
 
Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011Report This Post
DMA
Gold member
posted Hide Post
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.


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Venkat-
Silver Member
posted Hide Post
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-,


product release:8203
o/s: windows 10
expected o/p formats: HTML,EXCEL,PDF
 
Posts: 34 | Location: Kearney, Nebraska | Registered: December 14, 2011Report This Post
Dan Satchell
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
DMA
Gold member
posted Hide Post
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.


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Dan Satchell
Virtuoso
posted Hide Post
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.


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
j.gross
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
DMA
Gold member
posted Hide Post
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.


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
George Patton
Master
posted Hide Post
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.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
George Patton
Master
posted Hide Post
Another thought - maybe the new version of Focus doesn't like your field names.... just a thought.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
DMA
Gold member
posted Hide Post
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?


WebFOCUS v. 7.6.8 || Windows XP || Format Output: HTML, PDF, EXCEL
 
Posts: 57 | Location: NYC | Registered: November 13, 2008Report This Post
Danny-SRL
Virtuoso
posted Hide Post
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.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
j.gross
Virtuoso
posted Hide Post
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?
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders