 |
|||||
|
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 |
| is this a focus problem? | Login/Join |
| Member |
Please help me to slove this problem. I am joining two tables table A contains userid, Fname, Lname table B contains userid, groupID Table A has some userid that may or may not be in table B Since I want to list all the userid that are in table A, I have left outer join , multiple When I do not exclude any of the groupid then all those userid in table A show -up but when I exclude some or any one groupid, it will list only those userid that are present in both the tables. But I thought it should list all the userid in table A except those that are in excluded group. Please help. | ||
|
| Virtuoso |
Try setting ALL = ON or ALL = PASS May or may not help depending on your data source. Leah | |||
|
| Virtuoso |
Since you are doing a record selection on a field in table B, you need to tell focus to allow records that do not have a table B value to PASS the record selection. SET ALL = PASS will tell focus to let these records PASS the record selection. Thanks! Mickey
| |||||
|
| Member |
I tried both, SET ALL = PASS and also SET ALL = ON. but didn't work. any other suggestions please???? thanks | |||
|
| Virtuoso |
In the Master File Description (MFD) for Table B, does the GROUPID field have the MISSING=ON property? Thanks! Mickey
| |||||
|
| Member |
Yes, it does have MISSING = ON | |||
|
| Virtuoso |
Can you post some of your code for us to see how you coded the request? Also, what database are you using? Oracle? SQL Serve? FOCUS? DB2? Thanks! Mickey
| |||||
|
| Platinum Member |
Here's what I think is happening. With ALL=ON, the join is returning the records you want. If you did a TABLE FILE after the join, with no WHERE statements, you'd see the records. ALL=ON only tells FOCUS to not eliminate records in table A, even if there are no matches in table B. Your WHERE statement is operating on the join result set (after the join, basically), and you have told WebFOCUS to exclude all records that have a group id equal to some value, or set of values. Where there was no matching B record, group id is null, which, I think, means that the record will fail any equality check other than IS MISSING. You might try changing your WHERE to read something like: WHERE (GROUPID NE 'ABC') OR (GROUPID IS MISSING) I'm not sure of myself here. Have to see your masters and your code to be sure. Wouldn't hurt to see example data and what you expect the result to be. dwf | |||
|
| Member |
Yes, it works now. I had to do two things: WHERE (GROUPID NE 'ABC') OR (GROUPID IS MISSING) and SET ALL = PASS It seems that it does not matter whether I have missing on attributes in my master file or not as it works with or without missing on. Thank you all for your help. | |||
|
| 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 |