 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| JOIN problem with 5.26 | Login/Join |
| Member |
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 | ||
|
| Virtuoso |
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. | |||
|
| Expert |
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 | |||
|
| Virtuoso |
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; | |||
|
| Member |
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. | |||
|
| Expert |
jeez, that sounds like a call to TechSupport and an upgrade 'feature'; there was another one a few posts down. | |||
|
| Member |
Pietro, I tried your suggestion, and this also fails. Thanks | |||
|
| Silver Member |
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. | |||
|
| Member |
Jimbo, thanks for the suggestion, but alas, this also fails. | |||
|
| Virtuoso |
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 | |||
|
| Expert |
Tom, we're all really worried now  If this is busted, we're all in serious upgrade trouble. What does Tech Support say?? | |||
|
| <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 | ||
|
| Member |
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. | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |