Dyanmic Query with Static Temp Table

[Dawn M]

Progress 9.1C

Background: I have a static temp table that needs to be populated using a dymanic query. This temp table will be used to fill a freeform browser.

DBTableA contains:
lineNo (integer), seq (integer), createDate (date), description (char).

DBTableB contains:
lineNo (integer), seq (integer), BlineNo (integer), field1 (char), field2 (char).

Here's the code:

define temp-table ttTempTable no-undo
field lineNo as integer
field seq as integer
field createDate as date
field description as character
field field1 as character
field field2 as character.

define variable xforEach as character no-undo.
define variable xqueryString as character no-undo.
define variable xtableList as character no-undo.
define variable hQuery as handle no-undo.
define variable hBuffer as handle extent 2 no-undo.
define variable hTempTable as handle no-undo.
define variable hBufTempTable as handle no-undo.
define variable hTTField as handle no-undo.
define variable hQueryField as handle no-undo.
define variable i as integer no-undo.
define variable xok as logical no-undo.

/*** Assign Variables ***/
assign xtableList = "DBTableA,DBTableB".

/*** Assign Temp Table Handles ***/
assign hTempTable = temp-table ttTempTable:handle
hBufTempTable = hTempTable:default-buffer-handle.

/*** Create Dynamic Query ***/
create query hQuery.

/*** Create Dyamic Buffer for Query & "Set It" ***/
do i = 1 to num-entries(xtableList):
create buffer hBuffer for table entry(i, xtableList).
hQuery:add-buffer(hBuffer) no-error.
end.

/*** Create For Each Clause ***/
assign xforEach = "for each DBTableA no-lock," +
" each DBTableB of DBTableA no-lock ".

/*** Append Sort & Where Clauses ***/
assign xqueryString = xforEach + " where " +
(if xwhereClause <> "" then xwhereClause else "") +
(if xsortClause <> "" then xsortClause else "").

/*** Prepare Query ***/

xok = hQuery:query-prepare(xqueryString).

if xok = no then do:
message "Invalid Query. Please contact MIS."
view-as alert-box error.
return error.
end.

/*** Open Query ****/
xok = hQuery:query-open().

/*** Get the First Record ***/
xok = hQuery:get-first().

/*** Process Query and Create Temp Table ***/
QUERY-LOOP:
do while not hQuery:query-off-end:

/*** Get Next Record ***/
xok = hQuery:get-next().

if xok = yes then do:

/*** Create Temp Table ***/
hBufTempTable:buffer-create().

/*** PROBLEM IS HERE ****/

end. /* if xok = yes */

end. /* QUERY-LOOP */

/*** Close Query, Release Buffer & Delete Handles ***/
hQuery:query-close().

delete object hBuffer[1].
delete object hBuffer[2].
delete object hQuery.

The problem is when I'm trying to assign the fields from DBTableA & DBTableB to my temp table ttTempTable.

I've tried the following:

hTTField = hBufTempTable:buffer-field("lineNo").
hQueryField = hQuery:buffer-field("lineNo").

hTTField:buffer-value = hQueryField:buffer-field.

But got the error "BUFFER FIELD is not a queryable attribute for QUERY widget".

I really can't use BUFFER-COPY, because I would be excluding more fields from DBTableA then ones I'm assigning.

What am I doing incorrectly?

Any suggestions would be greatly appreciated!

 

[plus_marca]

Hi.

You have to access the buffer that has the field you want

like this.

hTTField = hBufTempTable:buffer-field("lineNo").
/* to access field lineNo in table DBTableA */
hQueryField = hBuffer[1]:BUFFER-FIELD("lineNo").
/* now you can copy the data */
hTTField:buffer-value = hQueryField:BUFFER-VALUE.

AND for fields in table DBTableB.

hTTField = hBufTempTable:buffer-field("field1").
/* to access field field1 in table DBTableB */
hQueryField = hBuffer[2]:BUFFER-FIELD("field1").
/* now you can copy the data */
hTTField:buffer-value = hQueryField:BUFFER-VALUE.

Hope this helps.

 

[Dawn M]

I think it does. Thanks!