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     JOIN problem with 5.26
Go
New
Search
Notify
Tools
Reply
  
JOIN problem with 5.26
 Login/Join
 
Member
posted
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
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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;
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Reply With QuoteReport This Post
Expert
posted Hide Post
jeez, that sounds like a call to TechSupport and an upgrade 'feature'; there was another one a few posts down.
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<Pietro De Santis>
posted
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
 
Reply With QuoteReport This Post
Member
posted Hide Post
Pietro,
I tried your suggestion, and this also fails.
Thanks
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.
 
Posts: 36 | Location: Melville,NY | Registered: August 09, 2004Reply With QuoteReport This Post
Member
posted Hide Post
Jimbo, thanks for the suggestion, but alas, this also fails.
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1317 | Location: Council Bluffs, IA | Registered: May 24, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
Tom, we're all really worried now Frowner
If this is busted, we're all in serious upgrade trouble. What does Tech Support say??
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Reply With QuoteReport This Post
<monte2000>
posted
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
 
Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 26 | Location: Mercy College | Registered: May 06, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     JOIN problem with 5.26

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