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     Joining Master Files - Need A Distinct Sub-Query

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Joining Master Files - Need A Distinct Sub-Query
 Login/Join
 
Johnny5
Gold member
posted
I have come across an issue that I don't know how to fix. I have a report that requires a join from a reference table to a fact table. The reference table is a dimension that has the highest to lowest levels of data. (example: Country, State, County, Employee)

I have a fact table that is about 80 million rows in Oracle that contains the State only. I need to join the two together and doing it as a hold file makes me believe it will then want to bring back the fact data on the application server.

In SQL I would do something like this:

select state, sum(x)
from
fact_table,
(select distinct state from reference)
where state = state.

How do I do this and keep the work on the database?
 
Posts: 62 | Location: Rochester, NY | Registered: September 30, 2005Report This Post
Leah
Virtuoso
posted Hide Post
If I understand your select, you want a sum of x for each state. In our db2 world I would

table file fact_table (or mfd name for it)
sum x
by state
end
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Leah
Virtuoso
posted Hide Post
Sorry in my previous post I missed something, so

join state in reference to all state in fact_table as j1
end
table file reference
sum x
by state
end
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Johnny5
Gold member
posted Hide Post
I am not 100% sure on your post. I read that as get the data summed by state and then join that back to my reference table on state.

The reference table would look something like this:
Reference Table

CC   ST   CITY

------------------

US   NY   BUFFALO
US   NY   SYRACUSE
US   NY   ROCHESTER

Fact Table
ST   Metric
-------------
NY   5
NY   10
NY   20
-------------
NY   35   *Result from SUM(Metric) by state

If I then join back I get this as a dataset:
NY   35  (match for Buffalo)
NY   35  (match for Syracuse)
NY   35  (match for Rochester)
--------------
NY   105 *results due to city records in reference
I'm somewhat new to the product so maybe what you said get me at the distinct states. Did I understand it correctly?

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


WF 5.3.5 / SOLARS 2.9 / Apache / Tomcat / Oracle (9.2/10g)
 
Posts: 62 | Location: Rochester, NY | Registered: September 30, 2005Report This Post
Leah
Virtuoso
posted Hide Post
No only the join from reference to fact_table and do the summing in the fex, however, you really want the 'unique sum for the state' correct? What do you want your report to be. Understanding what you want to do helps.
What I said earlier may not work as multiple references for state joining to multiple in fact_table may not get all your data. So back to square one so to speak.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Report This Post
Noreen Redden
Gold member
posted Hide Post
I'm not quire sure what you need here. Forgive me I think it is me, and not you. If your fact table has only the state and the amount, then why do you need the reference table at all, as SUM amount BY STATE should give you what you need. Once you include in the cities, then ytou do have a many-to-many situation. No, maybe you have other information at the STATE level, that you do need. I would suggest that in this case, JOIN might not be the way to go, but MATCH. Let's say that with the reference table, we have not only CITY, but the population of each city. So, I want a report showing the Total POPULATION and the total of the metric. Then, this will do the trick:
MATCH FILE REFERENCE
SUM POPULATION BY STATE
RUN
FILE FACT_TABLE
SUM METRIC BY STATE
AFTER MATCH HOLD AS COMBO
END
TABLE FILE COMBO
PRINT *
END
The last TABLE would of course have all of the stylesheet formatting, headings, etc.
 
Posts: 60 | Location: 2 penn | Registered: May 22, 2003Report This Post
EricH
Platinum Member
posted Hide Post
I concur with Noreen on not needing the join to the dimension table. In SQL you would simply do this:

select state, sum(x)<br />from<br />fact_table<br />group by state
which translates to first FOCUS code that Leah gave you:

table file fact_table<br />sum x<br />by state<br />end<br />
Why do you need the join to the dimension table?

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
Johnny5
Gold member
posted Hide Post
Sorry I should have mentioned that I'm using ID's and not actual state values. I did that for a simpler explanation.

In some cases I need to join because the will want something like give me all the states within the US. So my where clause is on Country and my group by is on state (or it could be city)

My reference table still has duplicate rows in either case when looking at state. So unless I do two distinct pulls it seems I can't get the right number.

I guess I'd like to do this in all one shot without using an intermediate hold file.

I have to look back through the examples that were posted to understand them a bit more.
 
Posts: 62 | Location: Rochester, NY | Registered: September 30, 2005Report This Post
EricH
Platinum Member
posted Hide Post
If you're using IDs to link from your fact table to your dimension table, then I'm even more baffled. In any dimension table, you need unique IDs for each entry; so your dimension table should look something like this:


Reference Table
ID   CC   ST   CITY
--   ---  ---  --------
11   US   NY   BUFFALO
22   US   NY   SYRACUSE
33   US   NY   ROCHESTER
However, you seem to be implying that you have duplicate IDs in your dimension table. In other words, the IDs are at the State level and you have redundant entries:

 
Reference Table
ID   CC   ST   CITY
--   ---  ---  --------
99   US   NY   BUFFALO
99   US   NY   SYRACUSE
99   US   NY   ROCHESTER
If you can give us the precise details of your situation with specific examples of the IDs in both the fact and dimension tables, we can help you figure out a solution.

EricH

This message has been edited. Last edited by: <Maryellen>,
 
Posts: 164 | Registered: March 26, 2003Report 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     Joining Master Files - Need A Distinct Sub-Query

Copyright © 1996-2020 Information Builders