Using Dynamic Queries on Dynamic Temp-tables

[cgdev]

Hi ProgressTalkers,

I am trying to develop a common program to compare two delimited flat files and generate a delta.

I have opted to use the dynamic objects to develop this program.

This following is the glance of the design:

1) Check both the files have same format (First row is the header/field name).
2) Load both the files into two temp-tables (Temp-table will be build dynamically
as the file format may change from file to file).
3) Open the Dynamic Query for one first table(for each...).
4) Try to get the same record from the second table using the unique index on both the tables.
5) If the record available in the second table then do the buffer-compare to check the data got modified.

I tried to use the field-name in the leftside and I am getting the error 7328 while preparing the query at step 4.

table contains(tt-tbl1) the following two fields:
Code
Description.

table contains(tt-tbl2) the following two fields:
Code
Description.

bufhdl1 = tt-tbl1:handle.
buffld = bufhdl1:buffer-field(1).
...
qrystr = "for each tt-tbl where Code = '" + buffld:buffer-value + "'".
...

Could some one help me on how to generate dynamic queries on dynamic temp tables i.e. Adding Where Clause on a dynamic temp-table. I am using progress 9.1D.

I appreciate your earliest reply.

 

[jongpau]

Hi,

If you are using Progress 9.1D, you can use a dynamic find on thesecond temp-table instead of preparing and executing a query for eachiteration of the query on the first table. Because you can now (since9.1D) use multiple attributes you could create single statement thatdoes this:

tt-tbl2:BUFFER-HANDLE:FIND-FIRST("WHERE Code EQ '" +STRING(tt-tbl1:BUFFER-HANDLE:BUFFER-FIELD("Code"):BUFFER-VALUE) + "') NO-ERR0R.
IF tt-tbl2:BUFFER-HANDLE:AVAILABLE
THEN DO:
  ... do whatever you need to do here when the record exists ...
END.

In the above I am assuming that tt-tbl1 and tt-tbl2 are thehandles of your dynamic temp-tables. Note that I did not test theabove, it's "coded" straight into this post so you may have to tinkerwith it a little. Also, if the imported files are rather large I wouldsuggest you create an index on the "Code" fields of the dynamictemp-tables (if you have not done so yet already).

HTH

 

[cgdev]

Hi Paul,

Thanks a lot for the code snippet. It works great for static temp-tables or database tables.

This code fails when I tried to use this code with dynamic temp-tables. I am still getting the error 7328.

PROGRESS could not able to understand when I use the field name of the dynamic temp-table.

Is there a way how I can refer the field names of the dynamic temp-table in the query.

Or, Can't we build query at run-time on a dynamic temp-table?

I appreciate your help in this regard.

 

[jongpau]

Can you post the code of how you create your dynamic temp-tablesplease? That way it is possible to see a little more of how you havedone things and it might be possible to work out where the error comesfrom. The Progress documentation for the error tells that either yourtable name or the field name you use are ambiguous...

And, yes you can build dynamic queries on database tables, static temp-tables AND dynamic temp-tables