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     Please help: Many to Many

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Please help: Many to Many
 Login/Join
 
<bigpgo>
posted
Hi, I'm trying to do a Many:Many LEFT outer join. So, if I have tables T1 and T2:

T1:
A 1
A 2
C 1

T2:
A "x1"
A "x2"
A "x3"
B "x1"

I want the result to be:
A 1 "x1"
A 1 "x2"
A 1 "x3"
A 2 "x1"
A 2 "x2"
A 2 "x3"
C 1 " "
None of the MATCH relationships (printRazzerrint, sumRazzerrint, print:sum, sum:sum) seem to capture this. Any help appreciated.
 
Report This Post
Silver Member
posted Hide Post
you might try:

SET ALL=ON
JOIN KEY IN T1 TO ALL KEY IN T2 AS J1_
TABLE FILE T1
PRINT VAL1 VAL2
BY KEY
BY VAL1 NOPRINT
BY VAL2 NOPRINT
END

the output is:

PAGE 1


KEY VAL1 VAL2
--- ---- ----
A 1 X1
1 X2
1 X3
2 X1
2 X2
2 X3
C 1 .

and the masters:

FILENAME=T1,SUFFIX=FOC
SEGNAME=TOP,SEGTYPE=S2
FIELDNAME=KEY,,A1,FIELDTYPE=INDEX,$
FIELDNAME=VAL1,,I5,$

FILENAME=T2,SUFFIX=FOC
SEGNAME=TOP,SEGTYPE=S2
FIELDNAME=KEY,,A1,FIELDTYPE=INDEX,$
FIELDNAME=VAL2,,A5,$

hth,

drew
 
Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003Report This Post
<bigpgo>
posted
Drew, thanks for the reply. Unfortunately, this doesn't seem to work because joins do not support many to many relationships. The first "A" in T1 matches all 3 entries in T2, but the second "A" does not get paired up with anything.
 
Report This Post
Silver Member
posted Hide Post
i made a slight change to my code:

SET ALL=ON
JOIN KEY IN T1 TO ALL KEY IN T2 AS J1_
TABLE FILE T1
PRINT KEY VAL1 VAL2
BY KEY NOPRINT
BY VAL1 NOPRINT
BY VAL2 NOPRINT
END

to better demonstrate the data.


PAGE 1


KEY VAL1 VAL2
--- ---- ----
A 1 X1
A 1 X2
A 1 X3
A 2 X1
A 2 X2
A 2 X3
C 1 .


and here is the t1 data:

PAGE 1


KEY VAL1
--- ----
A 1
A 2
C 1

and t2 data:

PAGE 1


KEY VAL2
--- ----
A X1
A X2
A X3
B X1


the other thing you might try is SQL passthru with a left outer join. That works for SQL or FOCUS datasources.

hth,

drew
 
Posts: 46 | Location: San Francisco, California | Registered: April 14, 2003Report This Post
<bigpgo>
posted
Drew, thanks again for the reply. I must be on an older version of webFOCUS - I'm just not getting the output that you're getting (true to some IBI document on JOINs that I found, many:many is not working with JOINs. It finds a match for the first entry, say, "A", but since it never backs up, it prints a blank when tryin to match up the second instance of "A"). The only thing that SET ALL=ON is doing for me is, it mimics a left outer join by including parent instances with no child instances, but it still is unable to force a many:many join. Also, like you've said, I tried doing this by inserting straight SQL, but my version of webFOCUS does not seem to like it - I get an error on anything other than a regular (inner) join.

I saw a document that talked about MATCH supporting many:many, although I haven't been able to get it to work. Any ideas on how to use that? Thanks a lot.
 
Report This Post
Platinum Member
posted Hide Post
Try

SET CARTESIAN = ON

EricH
 
Posts: 164 | Registered: March 26, 2003Report This Post
<bigpgo>
posted
Thanks, but that didn't work either =(
Any ideas on how to maybe try using MATCH to get a many:many ? Thanks.
 
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     Please help: Many to Many

Copyright © 1996-2020 Information Builders