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] how to get 1 report from 5 tables

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] how to get 1 report from 5 tables
 Login/Join
 
Krishna
Member
posted
I have 5 SQL Server tables I have to join to get the data for a report. When I join them, I get one parent and 4 sibling segments, and can't report off this structure.

What is the best way to do this so I can get fields from all the tables in one report?

This message has been edited. Last edited by: Kerry,


Version: 7.7.3
Windows 7
PDF, Excel, HTML
 
Posts: 13 | Registered: December 26, 2011Report This Post
Waz
Expert
posted Hide Post
Two options,

1. TABLE FILE...PRINT all fields needed, then HOLD then report

2. Change your join structure, if possible, to a single path, not a tree.


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
FrankDutch
Virtuoso
posted Hide Post
To give you better advice you need to give us more information about the tables and the relation they have.




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Krishna
Member
posted Hide Post
Tables all have the same key field. Table "A" has 1 record per key field. Tables "B", "C", "D", and "E" have many records per key field.

Some of the field names are the same in more than one table, but have different data, so have to specify which table those fields are coming from, but that gives errors.

TABLE FILE...PRINT gives error messages.

How do I make the join structure into a single path?

This message has been edited. Last edited by: Krishna,


Version: 7.7.3
Windows 7
PDF, Excel, HTML
 
Posts: 13 | Registered: December 26, 2011Report This Post
eric.woerle
Master
posted Hide Post
Krishna,

Can you post the error message that you are getting. It is very difficult to give advice on an issue when you don't tell us what it is.

As far as join structures are concerned, the standard structure for a join would be

 
JOIN FIELD1 IN A TO FIELD1 IN B AS J0
JOIN FIELD2 IN A TO FIELD2 IN C AS J1
JOIN FIELD3 IN A TO FIELD3 IN D AS J2
JOIN FIELD4 IN A TO FIELD4 IN E AS J3
END


this will make table A the parent and the other four children of A. Thats just how WebFOCUS looks at it. If you wanted to chose a specific field from a specific table you would write it something like this:

TABLE FILE TABLEA
PRINT
TABLEC.SEGMENTC.FIELD3  
BY TABLEA.SEGMENTA.FIELD1
END


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
FrankDutch
Virtuoso
posted Hide Post
You can not have many records per key field unless it has a second or third key field.
Can you make a picture of how the data is structured...or why you have a table B or C or D.

Table file .... Print does not work
But check file .... Picture might give you a view on what you have.

How about some training? Reading the manuals?




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Waz
Expert
posted Hide Post
Can you give samples or examples of the data in the 5 tables, and what the result should look like ?


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
<Emily Duensing>
posted
Trying making all of your joins unique (one to one) joins. Even though they are NOT one to one, the interface will now see the structure as a single path because all of the children become logical extensions of the parent. So, no more error on multiple paths... HOWEVER... you have to be able to understand the data you are getting back and the consequences of how it is returned to you. Since I don't know what data you are looking at, I cannot give specifics, but you will need to be aware of duplicate fields, fields that you might sum that could be inaccurate because of format of the returned result, etc.

I used to use this technique all the time in a DB2 environment to get around things... you just have to be prepared to possibly do some cleanup once you have all the data back to WebFOCUS.

Good luck!
 
Report 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] how to get 1 report from 5 tables

Copyright © 1996-2020 Information Builders