Dynamic query with self join - Forum - OpenEdge Development - Progress Community

Dynamic query with self join

This question is not answered

I have very challenging situation with self join and dynamic query.

Here is the scenario :  

In a smart window the records are displayed in a browser based on the selection of date.

    The data to be displayed is retreived from two tables:   

                   1. Table_A with self join on it.      

                   2. Table_B with SourceID.Table_A = SourceID.Table_B     

                           Retrieve Sourcename from Table_B for the SourceID    

 Here is the query :    

 Define buffer x2 for Table_A.

   FOR EACH Table_A NO-LOCK,        

    EACH x2 WHERE x2.SOURCE_Id = Table_A.SOURCE_Id        

             AND DATE(Table_A.DATE_Time_Updated) = DATE ("03/26/2009")         

             and (x2.QSA_Approved = YES  OR x2.QSA_Approved = NO),      

EACH Table_B WHERE Table_B.Source_Id = Table_A.SOURCE_Id           

BY Table_B.SOURCE         BY x2.DATE_Time_Updated DESC:   


 I  have following questions :     

1. How many SDO's do I need to create?  

 2. How the self join can be represented in SDO?  

 3. How the Dynamic query can be formed?     

I tried different options with buffers but nothing worked.  I am not able to figure out how to do DYnamic query.    

I will highly appretiate your great help. I am badly stuck with it.


All Replies
  • Do you really need to do the self-join? from what i am seeing in your query, there is no reason why you are doing some of it.

    Couldn't you simply do:

    FOR EACH Table_A NO-LOCK WHERE DATE(Table_A.DATE_Time_Updated) = DATE("03/06/2009") AND ( Table_A.QSA_Approved = YES OR Table_A.QSA_Approved = NO), EACH Table_B NO-LOCK WHERE Table_B.Source_ID = Table_A.SOURCE_Id BY Table_B.SOURCE_Id BY Table_A.DATE_Time_Updated DESC:



    Would save a lot of query time i would think.

    If you want to do a dynamic query (where input data could cause a change in how the query itself is structured) then you want to use a query itself.

    def var hquery as handle.

    def var cwhere as character.

    cWhere = "FOR EACH Table_A NO-LOCK".

    cWhere = cWhere + " WHERE DATE(Table_A.DATE_Time_Updated) = DATE(03/06/2009)".

    cWhere = cWhere + ", EACH Table_B NO-LOCK WHERE Table_B.Source_ID = Table_A.Source_ID".

    open query hquery.

    hquery:add-buffer(buffer Table_A).

    hquery:add-buffer(buffer Table_B).



    Though that is all taken from memory as i don't have my normal work pc in front of me.

  • Thanks. But I have to use self join because the required results are not displayed without self join.

    Could you please help with  displaying the results in browser based on self join.

    Hi Jasdeep,
    May I suggest that you open a support case so we can help you in a more in depth manner?
    Thanks, Brian Maher

  • I never opened a case before . Could you please tell me how I can do it? Thanks for your help.

    Hi Jasdeep,
    There should be a button available to you on this thread that you can just click to open a case.

  • Thanks Brian. It kind of strange there is no button for support on my thread.

  • Architect of the SmartComponent Library and WinKit

    Consultingwerk Ltd.

    The missing button indicates that your communities login is not tied to an active support contract.   If you know you have an active support contract or if you wish to purchase one, go to the SupportLink portal here ... http://www.progress.com/support-and-services

  • I opened a support case case .Do you need any infor from me.

  • Can somebody please suggest me , how "support Cases" work . I opened a case for Self join and displaying records in browser. I dont know what to do next. Should I provide case number. This is my firs time opening case with progress

    Hi Jasdeep,
    A case number would be great.

    Hi Jasdeep,
    Depending on what region you are in a support engineer from that region will contact you directly.

  • >> Thanks. But I have to use self join because the required results are not displayed without self join.

    This query will return duplicate rows if there is more than one Table_A of the requested date. (unless there is a unique index that prevents it).  This is typically not desirable.

    It is fully possible to define this query with a buffer in an SDO and browse the records, but this SDO should not be used to update data. The SDO do not refresh/sync other occurrences of the same record after an update .  

    Support will be able to show you how to define this join in the SDO.  

    I suspect it is not possible to write a query that eliminates the duplicate entries (I could be wrong).  In that case you might fill a temp-table with a single record even if there are many Table_A for the criteria and use this as the top record in the query instead of the Table_A.