Can anybody tell me how to do self join. For instance I want to get data from employees table , who reports to whom.
Thanks.
The information of self join in ABL is explained in following post:
define buffer manager for employee.
for each employee:
find manager where manager.id = emplyee.manager_id.
display employee.name manager.name.
end.
Thanks Gus. I tried it , it is giving "unknown or giving ambiguios table X1.(545)." error.
Here is actually what I try to implement.
In a table I am looking for records for specific date . The date is when some changes are made to one of the field of table.
The records that come up for that date ,for every record I want to show all the dates if any changes are done prior or after the selected date for that record.
So if the search is for date "3/4/14" , then it should show the following records:
ID Date Changed_Value
abc 2/3/14 yes
abc 3/4/14 no
xyz 3/4/14 no
xyz 6/7/14 no
xyz 5/6/14 yes
ghk 3/4/14 no
fgt 3/4/14 yes
In the above table the Changed_Value column refers to value that is changed . It can be changed between yes or no.
IT DOES NOT MEAN THAT THE VALUE IS CHANGED OR NOT. IT IS THE ACTUAL VALUE.
For records with ID abc and XYZ it shows the records for "3/4/14" along with other dates on which the value has been toggled from yes to no or from no to yes.
I am using following code:
define buffer x2 for RFSourceHistory.
for each RFSourceHistory where date(x2.DATE_Time_Updated) = DATE("03/04/2014"):
FIND x2 WHERE x2.Source_Id = RFSourceHistory.Source_Id .
DISPLAY x2 EXCEPT comment WITH STREAM-IO WIDTH 100.
END.
Please help me in correcting the code. It is driving me nuts.
Thanks
There must be something else. There is no X1 in the code you have shown. Which line is giving the error?
There must be something else. There is no X1 in the code you have shown. Which line is giving the error?
Flag this post as spam/abuse.
Click here to report this email as spam.
Right, "for each RFSourceHistory where date(x2.DATE_Time_Updated) " should be
for each RFSourceHistory where date(RFSourceHistory.DATE_Time_Updated) , but that doesn't explain X1.
Should be a lock phrase on the for each too.
Thanks Thomas, James, Gus and Swathi for your help. It worked . I was doing very stupid mistake. Thanks Thomas and James for pointing to it.
Another question. How can the self join for each statement can be presented using DYNAMIC-FUNCTION .
In other words , How can we SET THE SDO's WHERE CLAUSE, AND OPEN THE QUERY with SELF JOIN .
DYNAMIC-FUNCTION('addQueryWhere' IN h_sdo_For_RFTable,
cWhereClause,
"",
"").
DYNAMIC-FUNCTION('openQuery' IN h_sdo_For_RFTable).
Basically I need to display records in the smart data browser .
The following code works in procedure editor .
define buffer x2 for RFSourceHistory.
for each RFSourceHistory where date(RFSourceHistory.DATE_Time_Updated) = DATE("03/26/2009"):
FOR EACH x2 WHERE x2.Source_Id = RFSourceHistory.Source_Id and (x2.QSA_Approved = YES OR x2.QSA_Approved = NO)
BY x2.SOURCE_Id BY x2.DATE_Time_Updated:
DISPLAY x2 EXCEPT comment WITH STREAM-IO WIDTH 100.
END.
END.
I highly appreciate your help.
Thanks
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.
Thanks .