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] CARTESIAN JOIN

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] CARTESIAN JOIN
 Login/Join
 
Shubha Manjit
Silver Member
posted
I have 2 tables(CAR and BIKE):
CAR:
BMW
BENZ
SUZUKI

BIKE:
BMW
SUZUKI
HARLEY DAVIDSON
HONDA

I want to do a cartesian join between these 2 tables where my output has all the brands of car and bike listed
OUTPUT:
BMW
BENZ
SUZUKI
HARLEY DAVIDSON
HONDA

I tried doing:
JOIN car.brand IN CAR TO ALL/MULTIPLE
bike.brand IN BIKE AS J0
END

TABLE FILE CAR
PRINT *
END

This behaves like a left outer join and gives me the output as:

BMW
BENZ
SUZUKI

I also tried :
JOIN car.brand IN CAR TO ALL/MULTIPLE
bike.brand IN BIKE AS J0
ON TABLE HOLD AS WOW
END

TABLE FILE WOW
PRINT *
END

This threw an error:
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: WOW


Im very new to webFocus and I have been sitting on this for too long now. Any help is appreciated..

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


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 29 | Registered: February 21, 2011Report This Post
FrankDutch
Virtuoso
posted Hide Post
what you want is not possible by a cartesion join
you should however do something like a union query


SET ASNAMES=ON
TABLE FILE CAR
PRINT MODEL
MORE
FILE BIKE
END


supposed all the fields in both tables have the same name and format




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
Shubha Manjit
Silver Member
posted Hide Post
Thanks Frank. This worked for the columns with the same name like u mentioned, but I need more than this.
Here is my real table structure and the real code...I have 2 tables PLANDATA and ACTUALDATA
Columns of PLANDATA:
GONBR JOBNO PLANAMT PLANDATE

Columns of ACTUALDATA:
GONBR JOBNO ACTUALAMT ACTUALDATE

I want to join these 2 tables to display all values of both tables:

GONBR JOBNO PLANAMT PLANDATE ACTUALAMT ACTUALDATE

How do I do this?

I might also want to add more tables to the join in my next task. How do I add the extra tables to this...

Once again thank u so much for your quick response


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 29 | Registered: February 21, 2011Report This Post
FrankDutch
Virtuoso
posted Hide Post
use defines to rename and change the format of the fields
and you can repeat this several times with other tables




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
You could also do a MATCH FILE, but Franks suggestion is probably the best solution.


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
RSquared
Guru
posted Hide Post
Do a SET=ALL before you do a JOIN


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
FrankDutch
Virtuoso
posted Hide Post
rsquared

join is NOT an option




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
Shubha Manjit
Silver Member
posted Hide Post
Thanks Frank and Waz
When I use define to rename and format the changes on the field(planamt and costamt), the values of both planamt and costamt is merged into a single column. This is not what I want. My output should still be GONBR, JOBNO, PLANAMT, PLANDATE, ACTUALAMT,ACTUALDATE. How do I achieve this?


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 29 | Registered: February 21, 2011Report This Post
j.gross
Virtuoso
posted Hide Post
Use MATCH FILE, along these lines:

MATCH FILE this
WRITE (list of dependent columns specific to This)
BY (fields in common)
RUN
FILE that
WRITE (list of dependent columns specific to That)
BY (fields in common)
AFTER MATCH HOLD OLD-OR-NEW
END
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
FrankDutch
Virtuoso
posted Hide Post
add an extra field that gives you control of the sources
for table a :
DEFINE
FIELDSOURCE/A1='A';
END

for the other table the same

now you can distinguise by table ...




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
Shubha Manjit
Silver Member
posted Hide Post
This works Smiler

Thanks a lot j.gross


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 29 | Registered: February 21, 2011Report This Post
Shubha Manjit
Silver Member
posted Hide Post
Thank you all for all ur time and help..


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 29 | Registered: February 21, 2011Report 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] CARTESIAN JOIN

Copyright © 1996-2020 Information Builders