Focal Point
using MORE with JOIN

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/6751087331

June 16, 2004, 11:33 AM
<ort>
using MORE with JOIN
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...
June 16, 2004, 01:02 PM
<Grzegorz>
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
June 17, 2004, 01:10 PM
<ort>
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
June 17, 2004, 01:46 PM
<Grzegorz>
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 ?
June 17, 2004, 10:15 PM
N.Selph
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.
June 20, 2004, 10:29 AM
<ort>
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 ?
June 21, 2004, 12:37 PM
ET
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.
June 25, 2004, 11:45 PM
N.Selph
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.