A way to join dynamic tables?

You are joining each record of the first table with each record of the second table.

You need to have a where clause joining the tables and you probably want a first instead of each

queryh:QUERY-PREPARE(SUBSTITUTE("FOR EACH &1, first &2 where &2.parentfield= &1.idfield ", tableh:NAME , ctableh:NAME)).

I don't understands what are you trying to do.

The query makes me think that you want to copy both tables to a third, "joined". But you are copiying the second table onto the first one.. ¿?

If both tables have the same fields, then you are overwritting the values on the first table, with values in the second one.

I don't get what is it that you are trying to achieve..

Definetely you need to provide a WHERE clause on the query to make a proper join, you have dynamic attributes and methods that will help you to know which fields to match, similarly to what you already did with table names.

If you clarify your question, it will be better, as maybe is not that what you need.

David.

cverbiest, problem is I won't know which fields will be in a given table since it's a dynamic system where any database table could be referenced.

David, there's only 2 tables. I have a table with values representing database values and another table representing intended changes and I'm trying copy the latter to the former to generate a before-image. Problem is record 1 of tableh maps to record 1 of ctableh and record 2 to record 2 and 3 to 3 etc. So I need to join the tables somehow.

You don't need to join them:

table1:TRACKING-CHANGES = TRUE.

FOR EACH table2:

FIND table1 NO-ERROR.

IF NOT AVAILABLE table1

  CREATE  table1.

BUFFER-COPY table2 TO table1.

END.

FOR EACH table1:

IF NOT CAN-FIND(table2)

  DELETE table1.

END.

table1:TRACKING-CHANGES = FALSE.

That will leave both tables "the same".

Your issues is knowing wich fields to use for finding the right record. That largerly depends on your database, but generally speaking you can "query" the table indices and pick those fields for joining the two tables.

If you have no guarantee about table structure, then there is no way of doing this in a secure way. I mean that Progress doesn't enforce any kind of primary key rule, so is up to you..

I think you should receive the list of fields that form the primery key in each table (a mapping list like used for DataSource).. Somewhere in the code, "someone" knows that..

Good luck.

I don't really understand your solution David unfortunately.

I finally figured out a way to do it in the end, this is the code I used in the end.

CREATE QUERY queryh.

       queryh:SET-BUFFERS(bufferh).

       queryh:QUERY-PREPARE(SUBSTITUTE("FOR EACH &1", tableh:NAME)).

       queryh:QUERY-OPEN().

       CREATE QUERY cqueryh.

       cqueryh:SET-BUFFERS(tableh).

       cqueryh:QUERY-PREPARE(SUBSTITUTE("FOR EACH &1", ctableh:NAME)).

       cqueryh:QUERY-OPEN().

tableh:TRACKING-CHANGES = TRUE.

       DO WHILE queryh:GET-NEXT() ON ERROR UNDO, THROW:

           cqueryh:GET-NEXT().

           bufferh:BUFFER-COPY(cbufferh).

       END.

tableh:TRACKING-CHANGES = FALSE.

Having two separate queries incrementing together seems to solve the problem.

My solution is not code, but pseudocode, and is almost exactly what you did.

I see a couple of potencial problems in your code:

- There is no 'BY', so there is no guarantee that both querys are traversing the tables in the same order

- You are not comparing "primary keys", so you can't be sure if you are stepping over the right record, which means you assume both tables have exactly the same records

Given the above assumptions, I don't see a reason to do what you are doing, because all you are doing is forcing records in table1 to be persisted in table2, "no matter what".

My conclusion is:

- You are missing some checks

- Or you are complicating a simpler process