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.
Thanks
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:
END.
???
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).
hquery:query-prepare(cWhere).
hquery:query-open().
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.
I never opened a case before . Could you please tell me how I can do it? Thanks for your help.
Thanks Brian. It kind of strange there is no button for support on my thread.
Try this 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
>> 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.