Focal Point
JOIN problem with 5.26

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

August 16, 2004, 04:20 PM
Tom Walker
JOIN problem with 5.26
We are encountering problems with JOINs specifically with version 5.26. We did not encounter this problem running the same programs with 4.6x, nor with version 5.25.

I created a simple program like the one below, extracting a student id and student name from a master file, then holding it. The hold file is then joined back to the master. The results show data from the hold file, but none from the master file.

TABLE FILE SIS_STU_ATTR_TB
PRINT STU_ID STU_NAME
WHERE RECORDLIMIT EQ 3
WHERE READLIMIT EQ 3
ON TABLE HOLD
END
-RUN

JOIN STU_ID IN HOLD TO STU_ID IN SIS_STU_ATTR_TB AS J1;

TABLE FILE HOLD
PRINT HOLD.STU_ID HOLD.STU_NAME
SIS_STU_ATTR_TB.STU_ID SIS_STU_ATTR_TB.STU_NAME
END
-EXIT


STU_ID STU_NAME STU_ID STU_NAME
10000001 BUNDY, AL
10000003 EHIMARE, BIDEMI
10000002 DRACULA, COUNT
August 16, 2004, 05:46 PM
Leah
In your hold join you have a semi-colon at the end of the join. Is this just a typo or do you have it in the code?

I've never seen that style before.

Also, as I use the SIS system from SCT, I don't know the affect of having a join to the student name in the field. From the name I am assuming you are DB2 version. I have 5.2 and 4.6 access. I may try your file. Keep you posted.
August 16, 2004, 06:18 PM
susannah
Hi Tom, are you sure both files (base and extract) are in STU_ID order? We don't know from your example what kind of file the first base one is.
If its a .foc, is STU_ID indexed?
If not, is the file ordered by STU_ID?
The hold ftm file has to be known to focus as being in STU_ID order, so PRINT NAME BY ID
would tell focus you've got it in the right order.
try making a separate key
DEFINE FILE BASE
MYKEY/A10=STU_ID;
END
TABLE FILE BASE PRINT NAME BY MYKEY
ON TABLE HOLD
END
JOIN MYKEY IN HOLD TO STU_ID IN BASE AS J1
..check stuff
..and see if that works
August 16, 2004, 06:18 PM
Leah
Other than the fact that I am blind in one eye and can't see out of the other. We have the 5.25 and 4.36 available. His code works in my environment fine and I am using alias names for the tables.

We haven't installed 5.26 yet. I wonder if it is a join syntax issue. Have you tried the

-set &echo = all;
August 16, 2004, 08:19 PM
Tom Walker
Thanks for the responses, as this is a vexing problem which impacts most of the production programs.

Keep in mind that this was working until we upgraded from 5.25 to 5.26 last week.

In the example that I posted, I dummied down the actual queries to the bare essentials. Yes, I am sure the stu_id is the key.

Interesting, if I join existing tables, there is no problem. Only when the is held, then a join is written involving the HOLD file, does the probem occur. I checked the data attributes (len, type) of the files, and they are ok.

Any help is appreciated.
August 16, 2004, 10:44 PM
susannah
jeez, that sounds like a call to TechSupport and an upgrade 'feature'; there was another one a few posts down.
August 18, 2004, 04:21 PM
<Pietro De Santis>
Have you tried naming the HOLD file? Jusat a thought...

TABLE FILE SIS_STU_ATTR_TB
PRINT STU_ID STU_NAME
WHERE RECORDLIMIT EQ 3
WHERE READLIMIT EQ 3
ON TABLE HOLD AS HOLD1
END
-RUN

JOIN STU_ID IN HOLD1 TO STU_ID IN SIS_STU_ATTR_TB AS J1

TABLE FILE HOLD1
PRINT HOLD1.STU_ID HOLD1.STU_NAME
SIS_STU_ATTR_TB.STU_ID SIS_STU_ATTR_TB.STU_NAME
END
-EXIT
August 18, 2004, 08:04 PM
Tom Walker
Pietro,
I tried your suggestion, and this also fails.
Thanks
August 18, 2004, 11:12 PM
jimbo
Tom
Have you tried using a "HOLD FORMAT FOCUS INDEX STU_ID". For whatever reason in the 5.2x versions joining using a straight "HOLD" statement
does not seem to work like it used to.
August 19, 2004, 12:41 PM
Tom Walker
Jimbo, thanks for the suggestion, but alas, this also fails.
August 19, 2004, 03:24 PM
Leah
Tom,

I've been watching the stream on this problem you are having with 5.2.6. Is your MFD really called SIS_STU_ATTR_TB. We at UNO use SCT's system generated MFD's and port them down to the WebFOCUS server. Just curious. Of course it sounds as if you used the utility to let WebFOCUS build your MFD's from the table definitions. If you have more than one DB2 region are you perhaps pointing to one region with one table and another with the other. I did that once myself and got very strange results.

Oh and thanks again for the view. Haven't put it in yet, but haven't had the opportunity since you sent it.

Leah Cross
August 19, 2004, 04:35 PM
susannah
Tom, we're all really worried now Frowner
If this is busted, we're all in serious upgrade trouble. What does Tech Support say??
August 20, 2004, 01:30 PM
<monte2000>
Hi Tom.

This is your friendly neighborhood Moderator chiming in to say that I have been watching this thread and feel that you should get in touch with Tech Support.

I did send our support personnel an email notifying them of your problem but I am sure that they will need more info. on release, config., etc. so you should contact them directly to get to the bottom of this.

Cheers,

Monica
August 21, 2004, 03:51 PM
Tom Walker
Leah, how are things in the Wild West? Haven't you tamed your DBA's yet? Yes, we use the SCT generated MFDs. We are using only one instance, but the problem lies not with the database tables, rather with the HOLD file. But thanks for the thought..

Monica, I submitted the problem to tech support on the same day my initial post was made. Sometimes I get a quick solution from the board, so it was worth a try.

Susannah, Renee is the case worker. As of Friday, there is no solution in sight.