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] How would you do this join?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] How would you do this join?
 Login/Join
 
Member
posted
All-

I'm new to focus and trying to write my first report.

the main point is that i'm experiencing issues joining table a and table b.

Please follow this example

1. Table A looks like this:
(ID, and Name)

001Joe Smith
002Jane Smith
003Mike Smith

Table B looks like this:

(ID, color)

001Red
001Green
001Blue
003Red

2. Now i have define on table B for a counter

were i to try to sum the counts by ID on table b by itself with the following:

SUM(CNTR)
ACROSS Color
BY ID
AS ''

This prints out

001 3
003 1

but i want the sum for table 2... so i join and print the tables together with the define. by

3. joining table a and table b by either way

JOIN
ID on A TO ALL ID on B
END

SET ALL ON;

or i join it this way

JOIN
ID on A to MULTIPLE ID on B

and then

PRINT
ID NAME COLOR

gives

001Joe SmithRed
001Joe SmithGreen
001Joe SmithBlue
003Mike SmithRed

(where's Jane?)

4 sadly, both ways gives me this output when i do a sum across:

Joe Smith 3
Mike Smith 1

5. What's the right join to get this output

Joe Smith 3
Jane Smith .
Mike Smith 1

Thanks kindly for your help,

Drew

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 6 | Registered: August 16, 2010Report This Post
Platinum Member
posted Hide Post
JOIN
LEFT_OUTER ID on A TO ALL ID on B
END


WF 7.7.02 on Windows 7
Teradata
HTML,PDF,EXCEL,AHTML
 
Posts: 165 | Registered: September 29, 2008Report This Post
Member
posted Hide Post
may all of my problems be solved as easily.

thank you.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 6 | Registered: August 16, 2010Report This Post
Virtuoso
posted Hide Post
Drew,

From this thread, I gather that you are joining 2 relational tables, hence the LEFT_OUTER option.
If you join 2 FOCUS files then you would use your initial syntax. However, please notice:

 
JOIN
ID IN A TO ALL ID IN B AS J1 {IN, not ON; use AS joinname}
END

SET ALL ON {WITHOUT A ;}
 


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Daniel-

I tried the following join:

-*----------------------------------------------
-*
-* 5 Join the Files
-*
-*______________________________________________

JOIN CLEAR *

JOIN
BUYER_NO IN UD0 TO ALL
NEXT_ID IN UD1 AS J5
END

SET ALL ON;

UD0 and UD1 stand for User Defined 0 & 1. They are referencing hold files, not fomented as focus.

It did not do a left outer join.

thanks

drew


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 6 | Registered: August 16, 2010Report 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] How would you do this join?

Copyright © 1996-2020 Information Builders