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     using MORE with JOIN

Read-Only Read-Only Topic
Go
Search
Notify
Tools
using MORE with JOIN
 Login/Join
 
<ort>
posted
Hi everybody,

I'm tring to merge 2 tables using MORE.
My problem is to use JOIN for both tables.
My code looks like this:

JOIN CLASS_HISTORY.STUDENT_ID IN CLASS_HISTORY
TO STUDENTS.STUDENT_ID IN STUDENTS AS J1
END

TABLE FILE CLASS_HISTORY
PRINT
.....
MORE
FILE CLASS
.....
END

I want to join CLASS with STUDENTS because I want to use fields form STUDENTS in the PRINT command.

thank you for your help...
 
Report This Post
<Grzegorz>
posted
Maybe I did not understand your question correctly, but I think that it just should work, assuming that tables CLASS_HISTORY and CLASS have the same (or sufficiently similar) structure:

JOIN CLEAR *
JOIN CLASS_HISTORY.STUDENT_ID IN CLASS_HISTORY
TO STUDENTS.STUDENT_ID IN STUDENTS AS J1
END
JOIN CLASS.STUDENT_ID IN CLASS
TO STUDENTS.STUDENT_ID IN STUDENTS AS J2
END
-RUN
TABLE FILE CLASS_HISTORY
PRINT
.....
MORE
FILE CLASS
.....
END
 
Report This Post
<ort>
posted
Hi Grzegorz,

I tried your suggestion, but I'm getting this error:

0 ERROR AT OR NEAR LINE 115 IN PROCEDURE _ADHOCRQFOCEXEC *
(FOC955) THE FIELD DOESN'T HAVE A UNIQUE FLDNAM, OR ALIAS: STUDENT_ID
 
Report This Post
<Grzegorz>
posted
It is difficult (at least for me, maybe someone else encountered similar situation) to guess what can be a reason. I just tried with the simple example:

APP HOLD TESTS

TABLE FILE MOVIES
PRINT *
WHERE CATEGORY LIKE 'C%' OR
CATEGORY LIKE 'M%'
ON TABLE HOLD AS MOVIE1 FORMAT FOCUS
END

TABLE FILE MOVIES
PRINT *
WHERE CATEGORY NOT LIKE 'C%' AND
CATEGORY NOT LIKE 'M%'
ON TABLE HOLD AS MOVIE2 FORMAT FOCUS
END

JOIN CLEAR *
JOIN MOVIE1.MOVIECODE IN MOVIE1
TO RENTALS.MOVIECODE IN VIDEOTRK AS J1
END
JOIN MOVIE2.MOVIECODE IN MOVIE2
TO RENTALS.MOVIECODE IN VIDEOTRK AS J2
END
-RUN
TABLE FILE MOVIE1
PRINT CATEGORY TITLE TRANSDATE MOVIECODE
MORE
FILE MOVIE2
END

... and it works (WF 5.2.3 on Windows).
One another thing: did you use -SET &ECHO=ALL;, CHECK FILE, and CHECK FILE PICT to diagnose ?
What are the results ?
 
Report This Post
N.Selph
Guru
posted Hide Post
The error is because STUDENT_ID is in both files (CLASS_HISTORY and STUDENTS - also CLASS and STUDENTS). MORE doesn't know which of the 2 to show (parent or child). You can qualify which one to show for the first request, but MORE won't understand it. (Even though it doesn't matter, they are the same since you joined on it).

The way I've gotten around this is to redefine the field you want to show:
DEFINE FILE CLASS
STUDENT_ID/I11=STUDENT_ID;
END
DEFINE FILE CLASS_HISTORY
STUDENT_ID/I11=STUDENT_ID;
END

You even don't have to qualify the the field in the define, it is smarter than MORE and knows which one to show.
You will have the same problem for any other field that exists more than once in the joined table.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
<ort>
posted
Hi N.Selph,

Thanks !! it helped.
But, I have one more question:
When I add in the print command fields that belong to table STUDENTS and run the report, the running becomes really slow (like 1 minute unlike without those fileld - takes 10 seconds)

What could be the reson ?
 
Report This Post
ET
Platinum Member
posted Hide Post
I'm assuming that the tables are your are going after are relational. In the IBM DB2 world I have noticed that when I print only the columns that happen to be in the index from the tables the requests runs at one speed. When I add columns that are not in the index, I notice the request runs slower. This may be a reason of why the request is slower as in the one case the request is satisfied reading only the indexes whereas the other request has to read indexes and then the table data pages.
 
Posts: 115 | Location: Chicago, IL | Registered: May 28, 2004Report This Post
N.Selph
Guru
posted Hide Post
If the tables are relational, I always would look at the SQL generated by iWay if the query is running slower. You may get some clues in there.
 
Posts: 391 | Location: California | Registered: April 14, 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     using MORE with JOIN

Copyright © 1996-2020 Information Builders