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     [SOLVED] Inner Join Hold File to Synonym (Master File)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Inner Join Hold File to Synonym (Master File)
 Login/Join
 
Brandon Andrathy
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: 152 | Registered: July 10, 2018Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
jgelona
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: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
BabakNYC
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: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Brandon Andrathy
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: 152 | Registered: July 10, 2018Report This Post
Brandon Andrathy
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: 152 | Registered: July 10, 2018Report This Post
David Briars
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.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Brandon Andrathy
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: 152 | Registered: July 10, 2018Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Brandon Andrathy
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: 152 | Registered: July 10, 2018Report 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     [SOLVED] Inner Join Hold File to Synonym (Master File)

Copyright © 1996-2020 Information Builders