Forum Post: RE: Self join in Open edge

This depends a lot on how you want to work with the data and what kind of self join this is. You may consider using one SDO on the same table and drop one for each level and link them with data links joined on the self relation and assign the query to the top most SDO so that it only reads the top level. This allows you to update both (all) levels, but the update would need to be done in separate visual objects for each level. (This is straightforward if you have a simple parent - child self relation with two levels or a knwon number of levels, but can be somewhat of a challenge if you do not know have many levels there are, as in a Bill Of Material type of relationship. Querying a theoretically "endless" recursive BOM is complicated no matter which approach you use) You can also define a query that joins the tables in a single SDO. Keep in mind that a query is different from a for each in that it returns one result set, which gives a flattened view of the tables in the join. For an SDO this means that you can only update, create and delete the "child" (lowest) level as you cannot update the parent table in an SDO, since the same parent will be present in multiple rows. You can have the child level as the first in the join or last. In order to allow the SDO to query and join multiple instances of the same table, you need to define a buffer for the secondary table(s). This is done from the "Define Temp Tables" button on the query page of the SDO wizard when you create it. You select the option on the "Temp-Table" dialog that defines a buffer instead of a temp-table. You can then define the join between the table and the buffer(s) in the query dialog. This join will not change and should be part of the SDO's BaseQuery. (You can set the BaseQuery from code also if you do it very early). Note that the defined temp-tables and buffer as in this case, will show up as a separate "database" that you select on the top of the query dialog. After the join is specified you need to map the fields that you want to show from each buffer. If you want to see the same field from both parent and child these will need to have different names in the SDO. (The dialog will not allow you to have the same name twice and adds a sequence automatically when you map to a second database/temp-table field with the same name) NOTE: The join that you show in the example "x2 WHERE x2.Source_Id = RFSourceHistory.Source_Id" is likely wrong. This will make the same record appear as a child of itself. You will typically have a non unique "parent_source_id" field for the self join that is blank or unknown in the top level and use this to filter the top level and join to the child level.

Continue reading...