June 06, 2006, 05:58 PM
GraysailorMaster to a recursive join problems
Hello. I'm new to WebFocus, currently using 5.3.3 and preparing to upgrade to 7. I'm trying to figure out how to create a join that has a what seems to be complex structure. I have a master table with about 12 fields in it. I don't have any trouble joining it to most detail tables (mostly of the 'lookup' type, ie, the master has a surgeon employee code, and I've joined it to an employee table with the surgeon name) and a couple of one to many joins (where a patient number is linked to the case history table since a patient might have multiple cases).
Here is the problem.. I have a table (more than one actually) that is a recursive table. It has five fields or so, of which one field can be pointed at another and related. This works. However, when I try to join the Master (let's call it Cases) to this recursive (Sys_tree) it works, but when I try to THEN JOIN Sys_tree to itself to I cannot get anything. The Report Painter 'Splits' the joins into two windows and I cannot figure out how to use them. Any pointing in the right direction would be most helpful. I've read everything on recursive joins and understand at least some of it. My recursive joins by themselves work fine.
June 07, 2006, 03:46 AM
Tony AGraysailor, (hm interesting moniker)
When you create a join structure in WF with multiple tables, the subsequent joins are made to the new structure name which is the first table mentioned. Confused? Well let's try and make it clearer ....
Using your tables, the join would need to be something like -
JOIN CLEAR *
JOIN [field] IN Cases TO [matching field] IN Sys_tree (AS J1 TAG etc.)
JOIN [field in Sys_tree] IN
Cases TO [matching field] IN Sys_tree (AS J2 TAG etc.0
This will give you a structure like this -
Cases
|
|
Sys_tree
|
|
Sys_tree
which is what I think you are saying that you require?
T
June 07, 2006, 12:18 PM
GraysailorExactly. But when I attempt to do this, it splits the joins up and when I pull up Report Painter I do not see the Recursiv SYS_TREE files.
BTW - I'd rather be sailing, my last name is Gray (and hair getting there as well). I've got a Prindle Catamaran and a Columbia 27' Cruiser (lake cruiser).
June 08, 2006, 03:21 AM
Tony AI'm with you on the sailing, however, to do that I need to work
Post your join plus release and platform (as most have done in the signature section of the profile) and we'll see if we can assist more.
T
June 08, 2006, 09:25 AM
GraysailorI think I've got the profile signature done correctly. I forgot to add Tomcat - which I believe is also something we have. We are only weeks away from moving up to 7.
June 14, 2006, 11:17 AM
GraysailorI got it with help from IBI, it was my Tagging. I wasn't doing the correctly.