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     [SOLVED] Inner Join Hold File to Synonym (Master File)
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Inner Join Hold File to Synonym (Master File)
 Login/Join
 
Platinum Member
posted
Hello,

I am getting a list of unique records in one hold file and I want to join them up to my master file. This is a one to many relationship. How can we join hold files up to the synonym to filter down the records? The only way I am aware of is if I do a claims dump into a hold file and join from there (not efficient). Isn't IN FILE with 200,000 records not efficient either?

What is the most efficient way to do this? The records in the hold file would be about 200,000 and then records in the Master File are over 5,000,000.

Here's an example of the table structure I have

 

HOLD FILE 1
1
2
3
4
5
6
7
8
9
10
11
12

Synonym
1
1
1
2
2
3
4
4
4
4
4
5
5
6
6
6
7
7
7
8
9
9
10
 

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 114 | Registered: July 10, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
Where is your data coming from ? RDBMS ?

Is the hold file from the same DB ?

You could try FORMAT SAME_DB, then just join them, if its all the same DB ?


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: 6258 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Master
posted Hide Post
There is always MATCH FILE. We aren't allowed to create temporary HOLD files in the database so all of ours are WebFOCUS hold files. Sometimes, when there is a lot of data in the HOLD file, MATCH FILE has better performance (runtime wise) than JOIN and TABLE. Thing is, if you look at what WebFOCUS is doing, a lot of times it converts JOIN and TABLE to MATCH FILE.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 951 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
The most efficient way to do this is to make sure both files are in the same DBMS. Otherwise, you might unwittingly cause a scan of the first table before joining it to the second table. If this is an on the fly join, try to push everything into the same DBMS using HOLD FORMAT xyzdbms INDEX joinfieldname


WebFOCUS 8206, Unix, Windows
 
Posts: 1834 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hey all,

Thank you very much for your responses!

Babak, I'm thinking your suggestion regarding index might be the way to go. Could you give me an example of what you're thinking with the CAR File code I have below?

 TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1'
END

TABLE FILE CAR
SUM
	SALES
BY COUNTRY
ON TABLE HOLD AS 'CAR2'
END

JOIN
COUNTRY IN CAR1 TO UNIQUE
COUNTRY IN CAR2 TAG J0 AS J0
END 


WebFOCUS 8204
 
Posts: 114 | Registered: July 10, 2018Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Waz, could you also give me an example of what you are thinking with: SAME_DB PERSISTENCE GLOBAL_TEMPORARY

I got the index created on my second hold file with this. But I'm not sure where to go from here.


WebFOCUS 8204
 
Posts: 114 | Registered: July 10, 2018Reply With QuoteReport This Post
Master
posted Hide Post
Sounds like you are dealing with two relational tables.

Here is an example of creating a subquery to be used in a nested subquery SELECT:

http://forums.informationbuild...077033876#9077033876

You could also join to the table you created a 'SQL_SCRIPT' for.




Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2012/Tomcat Standalone Workstation: Windows 7/IE 11 Database: Oracle 12c/Netezza Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer, & ReportCaster

 
Posts: 793 | Registered: April 23, 2003Reply With QuoteReport This Post
Platinum Member
posted Hide Post
David, DBINFILE creates exactly the type of SQL I want to see!

Wow, I have used DBINFILE but not with FORMAT SQL_SCRIPT. It's insane how DBINFILE works differently with SQL_SCRIPT.

Thanks a ton for this solution! My Data guy is gonna be happy I won't be causing him headaches with crazy queries lol.

My resulting query looks like something like the following for anyone this might help:

  TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1' FORMAT SQL_SCRIPT
END

TABLE FILE CAR
SUM
	SALES
BY COUNTRY
WHERE DBINFILE(CAR1, COUNTRY, COUNTRY)
END
  


Is there anything I might be able to do to speed up DBINFILE? Maybe make sure indexes are created within the database?


WebFOCUS 8204
 
Posts: 114 | Registered: July 10, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
Assuming the data is all on the same DB, the first query (assuming it is coming direct from the DB, no intermediate hold files), you use FORMAT SAME_DB.

This will generate SQL to create a temp table and insert the records into it.
Then its a simple case of a join, how ever you like to do it.

e.g.
TABLE FILE CAR
BY COUNTRY
WHERE COUNTRY EQ 'FRANCE' OR 'ENGLAND'
ON TABLE HOLD AS 'CAR1' FORMAT SAME_DB
END

-RUN

JOIN
COUNTRY IN CAR1 TAG C1 TO UNIQUE
COUNTRY IN CAR  TAG C2 AS J0
END 

TABLE FILE CAR1
SUM
	C2.SALES
BY C1.COUNTRY
ON TABLE HOLD ....
END

-RUN



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: 6258 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you Waz. I appreciate your response. You've helped me a lot this week Smiler .

Closing this post.


WebFOCUS 8204
 
Posts: 114 | Registered: July 10, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Inner Join Hold File to Synonym (Master File)

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