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     [CLOSED] JOIN Question

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] JOIN Question
 Login/Join
 
Kamesh
Master
posted
When we join two tables with "ALL" option, why it is executing the sql (on background) for every record comparison.

Ex:

Join A in TabA to ALL B in TabB AS j0;

If we have 10 records in TabA and 10 records in TabB, it executes the SQL 10 times to compare the data.

Is there a way to make it execute only once to compare all the data.

Thanks in advance,

This message has been edited. Last edited by: <Kathryn Henning>,


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
Doug
Expert
posted Hide Post
Can you show us teh SQL Trace?
Are there DEFINE'd fields in the JOIN?
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
J
Guru
posted Hide Post
Also, are you joining across databases? You may need to rework your connections.


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Report This Post
Kamesh
Master
posted Hide Post
There are no DEFINE fields in the join and I am not joining across databases. Both the tables are in the same database.


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
J
Guru
posted Hide Post
Remind me if I am incorrect, but doesn't each field need to be checked to every other field with a join all?

That means in a 10x10 table, 100 comparisons are being made. I'm guessing it is executing for each record (1->10 for one execution).

Do you really want all the records joined to all the other records or am I thinking of the wrong "all"?


WebFOCUS 7.7.03/8.0.08
Dev Studio 7.7.03/8.0.08
App Studio 8.0.08
Windows 7
ALL Outputs
 
Posts: 402 | Location: Upland, IN | Registered: June 08, 2012Report This Post
Wep5622
Virtuoso
posted Hide Post
quote:
Originally posted by Kamesh:
Both the tables are in the same database.


Are the database CONNECTION strings in the access (.acx) files the same?
If you look at the master (.mas) files, do both have keys on the fields you are joining on?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1669 | Location: Enschede, Netherlands | Registered: August 12, 2010Report This Post
GamP
Virtuoso
posted Hide Post
quote:
When we join two tables with "ALL" option, why it is executing the sql (on background) for every record comparison

Define 'it' please. Do you see WebFOCUS issuing 10 requests (do you see 10 times the same sql being issued to the sql engine)? Or is the sql engine executing the single request from webfocus 10 times over? iaw: show us the sql that is being generated.


GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Kamesh
Master
posted Hide Post
1. One of the table is created by me (hold file of previous output) and joining that with the oracle table.

2. The SQL is getting executing for each number (key value).

ex: select * from tab a,tab b
where a.col1=b.col2
and a.key1=0001


select * from tab a,tab b
where a.col1=b.col2
and a.key1=0002


WFConsultant

WF 8105M on Win7/Tomcat
 
Posts: 780 | Location: Florida | Registered: January 09, 2005Report This Post
j.gross
Virtuoso
posted Hide Post
So your JOIN is across two servers -- between a table on the database server and a local table on the WebFOCUS reporting server.

You can create a Save file of the key1 values from the first HOLD, and add that as a filter to your second request:

WHERE key1 IN FILE (SAVE2);

That will translate into

where key1 in (value1, value2, ...) in the generated SQL Select.

But beware that (at least with regards to Oracle) there is a limit of 1000 values in such a filter phrase.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Michael_86
Member
posted Hide Post
quote:
Originally posted by J:
Remind me if I am incorrect, but doesn't each field need to be checked to every other field with a join all?

That means in a 10x10 table, 100 comparisons are being made. I'm guessing it is executing for each record (1->10 for one execution).

Do you really want all the records joined to all the other records or am I thinking of the wrong "all"?


Thats the way that I'm understanding it too J,

e.g
table 1
a
b
c

table 2

c
d
e

joined table

a c
b c
c c
a d
b d
c b
c c
c d
c e

So it will calculate all of these joins then apply your where clause Kamesh,

Personally if I was to join based on a couple of where clauses I would use a left outer join as it will do the join based on a where clause thus reducing the number of transactions it has to compute.

Plus I would break down the tab.a first so there are less lines to check

i.e.

select
*
from
tab b
left outer join
(Select
*
from
tab a
where
a.key1=0002
) ta
on (b.col1 = ta.col1)


Webfocus 7.7.03
Windows
Excel
 
Posts: 21 | Registered: February 08, 2013Report This Post
j.gross
Virtuoso
posted Hide Post
quote:
When we join two tables with "ALL" option, why it is executing the sql (on background) for every record comparison.


ONCE AGAIN: I has nothing to do with the "ALL". Any time you join a HOLD file to a Database Server table, and report against that Join structure, that is what happens.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report 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     [CLOSED] JOIN Question

Copyright © 1996-2020 Information Builders