Nesting dynamic queries

[Kladkul]

I'm a bit new to dynamic queries and don't fully understand them, mostly, I've been using code developed by other programmers to learn it. Here's a problem I've encountered that I'm not sure how to approach.

Heres the situation:

I have 2 dynamic temp-tables (t-hdl-tbl1, t-hdl-tbl2) and their buffers (tb-hdl-tbl1, tb-hdl-tbl2).

FOR EACH tbl1: 
     FIND FIRST tbl2 WHERE tbl2.item = tbl1.item NO-ERROR.

How do I do the above query dynamically? I've got the FOR EACH tbl1 portion down I believe:

ASSIGN v-table-name = tb-hdl-tbl1:NAME
           v-where = "FOR EACH " + v-table-name. 
 
CREATE QUERY v-hdl-query. 
v-hdl-query:SET-BUFFERS(tb-hdl-tbl1). 
v-hdl-query:QUERY-PREPARE(v-where). 
v-hdl-query:QUERY-OPEN(). 
v-hdl-query:GET-FIRST(). 
 
REPEAT WHILE NOT v-hdl-query:QUERY-OFF-END. 
     /* This is where the FIND FIRST goes */ 
END.

I also thought the v-where could just be bigger and have the FIND FIRST in it, but that didn't work.

 

[Casper]

Something like this:

define variable hQuery as handle no-undo.
 
create query hQuery.
hQuery:set-buffers(buffer customer:handle).
hQuery:query-prepare('for each ' + buffer customer:name + ' no-lock').
hQuery:query-open().
hQuery:get-first().
 
do while not hQuery:query-off-end:
    buffer salesrep:handle:find-first("where salesrep.salesrep = " + quoter(customer.salesrep)).
    display salesrep.salesrep.
    hQuery:get-next().
end.
 
delete object hQuery.

regards,

Casper.

 

[Casper]

I also thought the v-where could just be bigger and have the FIND FIRST in it, but that didn't work.

you mean like:

define variable hQuery as handle no-undo.
 
create query hQuery.
hQuery:set-buffers(buffer customer:handle,buffer salesrep:handle).
hQuery:query-prepare('for each customer no-lock, first salesrep where salesrep.salesrep = customer.salesrep no-lock').
hQuery:query-open().
hQuery:get-first().
 
do while not hQuery:query-off-end:
    display salesrep.salesrep.
    hQuery:get-next().
end.
 
delete object hQuery.

there is a difference between the 2, in your 4GL example you explicitely leave tb12 outside the for each.

Casper.

 

[AndrewSmith]

Deleted! Should read all posts first :-(

 

[Casper]

Casper

 

[Kladkul]

Thanks for the help, I believe the first example of yours will give me the desired results Casper. Going to test on this now! :biggrin:

I was confused at first, after looking at some of the training books I took last year, the book stated that you can't dynamically do a FIND FIRST since it's a 1 time search and not a query.

 

[Kladkul]

    buffer salesrep:handle:find-first("where salesrep.salesrep = " + quoter(customer.salesrep)).

Not sure I quite understand this statement. salesrep is a temptable? How will this work since the 2nd table is also a dynamic temp-table and it looks like your referring to it directly instead of through a handle?

 

[Casper]

well in this case salesrep is a normal database table from the famous sports2000 database. My examples work on the sports2000 database.

buffer salesrep:handle means the handle of the buffer for the table salesrep.
With this handle you can use the method find-first() the same as you would use it on a dynamic buffer.

it is the same as the dynamic counterpart:

create buffer hBuffer for table "salesrep".

see next example:

/* dynamic */
DEFINE VARIABLE hBuffer AS HANDLE      NO-UNDO.
CREATE BUFFER hBuffer FOR TABLE "salesrep".
hBuffer:FIND-FIRST().
MESSAGE hbuffer::salesrep
        VIEW-AS ALERT-BOX INFO BUTTONS OK.
 
hBuffer:BUFFER-RELEASE().
MESSAGE hbuffer:AVAILABLE
        VIEW-AS ALERT-BOX INFO BUTTONS OK.
DELETE OBJECT hBuffer.
/* buffer is deleted so no more dynamic values in play */
/* static */
BUFFER salesrep:HANDLE:FIND-FIRST().
MESSAGE BUFFER salesrep:HANDLE::salesrep
    VIEW-AS ALERT-BOX INFO BUTTONS OK.

Casper

 

[Kladkul]

Here's what I've come up with so far:

DO WHILE NOT v-hdl-query:QUERY-OFF-END: 
 
   /* t-hdl-tbl2 is tbl2's buffer */
   t-hdl-tbl2:HANDLE:FIND-FIRST("WHERE " + 
              QUOTER(t-hdl-tbl2:BUFFER-FIELD("fld1"):BUFFER-FIELD) + " = "
              + QUOTER(v-hdl-query:BUFFER-FIELD("fld1"):BUFFER-FIELD)) 
              NO-ERROR.
 
   MESSAGE QUOTER(t-hdl-tbl2:BUFFER-FIELD("fld1"):BUFFER-VALUE)
             VIEW-AS ALERT-BOX INFO BUTTONS OK. 
 
    v-hdl-query:GET-NEXT(). 
 
END.

Only problem is the value that is displayed is just the same value caught in a loop.

 

[Casper]

You mean like this:

DEFINE VARIABLE hbuf1 AS HANDLE NO-UNDO.
DEFINE VARIABLE hBuf2 AS HANDLE      NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE lTmp  AS LOGICAL     NO-UNDO.
ASSIGN hBuf1 = BUFFER customer:HANDLE
       hBuf2 = BUFFER salesrep:HANDLE.
 
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuf1).
hQuery:QUERY-PREPARE('for each ' + hBuf1:NAME).
hQuery:QUERY-OPEN().
hQuery:GET-FIRST().
DO WHILE NOT hQuery:QUERY-OFF-END:
    lTmp = hBuf2:FIND-FIRST('where ' + hBuf2:NAME + '.salesrep = ' +
                      QUOTER(hbuf1::salesrep)) NO-ERROR.
    IF hBuf2:AVAILABLE THEN
    DISPLAY hBuf1::salesrep hbuf2::salesrep.
    hQuery:GET-NEXT.
END.
DELETE OBJECT hQuery.a

in your example that would be:

DO WHILE NOT v-hdl-query:QUERY-OFF-END: 
    /* t-hdl-tbl2 is tbl2's buffer */
    t-hdl-tbl2:FIND-FIRST('WHERE ' + t-hdl-tbl2:NAME + '.fld1 = ' +
                                  QUOTER(t-hdl-tbl1:::fld1)).
 
    MESSAGE t-hdl-tbl2::fld1 t-hdl-tbl1::fld1
             VIEW-AS ALERT-BOX INFO BUTTONS OK. 
 
    v-hdl-query:GET-NEXT(). 
END.

I always use the shortcut <buffer-handle>::<fieldname> instead of writing:
buffer-handle:buffer-field('field-name'):buffer-value

HTH,

Casper.

 

[Kladkul]

Thats the answer I was looking for. Also, thanks for the short hand notation note. I remember seeing that somewhere during my training (just under a year ago) but forgot where it was used. Thanks.