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     [CLOSED] JOINING HOLD FILES ISSUE
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] JOINING HOLD FILES ISSUE
 Login/Join
 
Master
posted
I have not done this in quite some time. I am having issues doing an inner join on two hold files. I must be forgetting something pretty basic.

I have created something sample in the CAR file to illustrate. I guess the issue is the main hold file has multiple entries. The second hold file should be limiting the rows returned based on the inner join...it does not.

Try running this-it does not make much sense to do something like this..but I think it gets the point across...


-*
-* Hold File - All Cars and their Origins
-*
TABLE FILE CAR
BY LOWEST CAR.ORIGIN.COUNTRY
BY LOWEST CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_DTL FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

-*
-* For sake of illustration, create a second hold file with ONE record (Toyota)
-*
TABLE FILE CAR
BY LOWEST CAR.COMP.CAR
WHERE CAR.COMP.CAR EQ 'TOYOTA';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE HOLD AS HOLD_ONECAR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END


-*
-* Inner Join Hold file of all Cars/Origins to Hold File w/Toyota
-* Expect to get 1 entry--Get Multiple---not sure why?
-*
JOIN
INNER HOLD_DTL.HOLD_DTL.CAR IN HOLD_DTL TO UNIQUE HOLD_ONECAR.HOLD_ONE.CAR
IN HOLD_ONECAR TAG J0 AS J0
END
TABLE FILE HOLD_DTL
PRINT
HOLD_DTL.HOLD_DTL.COUNTRY
HOLD_DTL.HOLD_DTL.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 569 | Registered: June 28, 2013Reply With QuoteReport This Post
Virtuoso
posted Hide Post
1- Use FOCUS file
2- Index your key
3- Reverse your JOIN

TABLE FILE CAR
BY COUNTRY
BY CAR
ON TABLE HOLD AS HOLD_DTL FORMAT FOCUS INDEX CAR
END
-RUN
-*
-* For sake of illustration, create a second hold file with ONE record (Toyota)
-*
TABLE FILE CAR
BY CAR
WHERE CAR EQ 'TOYOTA';
ON TABLE HOLD AS HOLD_ONECAR FORMAT FOCUS
END
-RUN

-* 
-* Inner Join Hold file of all Cars/Origins to Hold File w/Toyota
-* Expect to get 1 entry--Get Multiple---not sure why?
-* 
JOIN
      CAR IN HOLD_ONECAR
   TO CAR IN HOLD_DTL  TAG J0 AS J0
END
TABLE FILE HOLD_ONECAR
PRINT COUNTRY
      CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
 INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/warm.sty,
$
ENDSTYLE
END


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
The basic rule for joining hold files is that the data has to be sorted by the same columns.

You have to consider that the join is a effectively a dumb join, as there are no indexes, its just a flat file with the order the rows are stored in. The join will read from the first file and check for records in the second. As soon as the sequence is out it will not join to anything.

You have BY COUNTRY BY CAR in the first and BY CAR in the second.

Change the first to be BY CAR BY COUNTRY, and it should work.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6209 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
Thank you both...I have either been lucky or just joining master files in the past I guess.

Which way is more efficient? I would think the FOCUS file.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 569 | Registered: June 28, 2013Reply With QuoteReport This Post
Master
posted Hide Post
FYI..not much luck with the non indexed solution. On the Indexed solution, why would the order of the tables matter in the join.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 569 | Registered: June 28, 2013Reply With QuoteReport This Post
Platinum Member
posted Hide Post
RobertF

Are you IT trained? Have you access to someone who can be seated with you to explain indexing and it is play in join?

Kofi


Client Server 8.1.05: Apache; Tomcat;Windows Server 2012
Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012
 
Posts: 104 | Registered: April 06, 2009Reply With QuoteReport This Post
Virtuoso
posted Hide Post
quote:
why would the order of the tables matter in the join


As suggested by Kofi a speak with a DBA or developer may help. And read about JOIN and connector in IB documentation.

But as fast answer it's all a question of parent-child, data hierarchy, connector properties and DB structure.
You normally put the must restrictive file (parent) at first and join to the child but it may depend on the DB structure (star, snowflake)

In your case the HOLD_ONECAR is used as a filtering table since you only want to see HOLD_DTL where CAR is in HOLD_ONECAR.
So, better to have HOLD_ONECAR in first place in the JOIN. The join will only exist for record that meet criteria

That said, you may use some SET or JOIN options to end with a totally different result even with the same code.
It result that way because the defaults SET and JOIN have been used.

This is the beauty of WebFOCUS


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2282 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Expert
posted Hide Post
From an efficiency point of view, you should think about overheads and time to do the join. Creating a HOLD file takes less time than a FOCUS file. Also as an example creating a HOLD file without sorting takes less time than one with sorting.

Also depends on number of records. The less the records the less impact.

There are many factors


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6209 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] JOINING HOLD FILES ISSUE

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