Self join in Open edge

Posted by jasdeep on 30-Jul-2014 18:48

Can anybody tell me how to do self join. For instance I want to get data from employees table , who reports to whom.

Thanks.

All Replies

Posted by Swathi Yellavaram on 30-Jul-2014 23:05

The information of self join in ABL is explained in following post:

community.progress.com/.../10643.aspx

Posted by gus on 31-Jul-2014 08:29

define buffer manager for employee.

for each employee:

   find manager where manager.id = emplyee.manager_id.

   display employee.name manager.name.

end.

Posted by jasdeep on 31-Jul-2014 11:52

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

Posted by Thomas Mercer-Hursh on 31-Jul-2014 12:44

There must be something else.  There is no X1 in the code you have shown.  Which line is giving the error?

Posted by James Palmer on 31-Jul-2014 12:49

The problem is in the where clause of the for each. It's referencing the buffer not the table. Can't correct right now as on phone.

James Palmer | Application Developer
Tel: 01253 785103

[collapse]From: Thomas Mercer-Hursh
Sent: ‎31/‎07/‎2014 18:46
To: TU.OE.Development@community.progress.com
Subject: RE: [Technical Users - OE Development] Self join in Open edge

Reply by Thomas Mercer-Hursh

There must be something else.  There is no X1 in the code you have shown.  Which line is giving the error?

Stop receiving emails on this subject.

Flag this post as spam/abuse.



Click here to report this email as spam.



Inenco, Ribble House, Ballam Road, Lytham, FY8 4TS. Company Reg No: 2435678 For further information on the Inenco Group Ltd please visit our web site at www.inenco.com

NOTE: This email and any information contained within or attached in a separate file is confidential and intended solely for the Individual to whom it is addressed. The information or data included is solely for the purpose indicated or previously agreed. Any information or data included with this e-mail remains the property of Inenco Group Ltd. and the recipient will refrain from utilising the information for any purpose other than that indicated and upon request will destroy the information and remove it from their records. Whilst this e-mail or attached documents may contain market information, this information is not provided as advice. Inenco do not accept any liability for any damages or losses of any kind suffered as a result of any action(s) taken as a result of the content of this e-mail or attachments. Any views or opinions presented are solely those of the author and do not necessarily represent those of Inenco Group Ltd. If you are not the intended recipient, be advised that you have received this email in error and that any use, dissemination, forwarding, printing, or copying of this email is strictly prohibited. No warranties or assurances are made in relation to the safety and content of this e-mail and any attachments. No liability is accepted for any consequences arising from it. If you have received this email in error please notify the sender by telephone on +44 (0)1253 785000. [/collapse]

Posted by Thomas Mercer-Hursh on 31-Jul-2014 13:18

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.

Posted by jasdeep on 31-Jul-2014 13:21

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.

Posted by jasdeep on 31-Jul-2014 16:20

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

Posted by Håvard Danielsen on 01-Aug-2014 08:26

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.  

Posted by jasdeep on 01-Aug-2014 15:48

Thanks .

This thread is closed