Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     using MORE with JOIN
Go
New
Search
Notify
Tools
Reply
  
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...
 
Reply With QuoteReport 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
 
Reply With QuoteReport 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
 
Reply With QuoteReport 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 ?
 
Reply With QuoteReport This Post
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, 2003Reply With QuoteReport 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 ?
 
Reply With QuoteReport This Post
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, 2004Reply With QuoteReport This Post
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, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     using MORE with JOIN

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.