Answered Nested Dynamic Queries

[Cringer]

I'm trying to build a nested dynamic query and struggling. When I say nested I want two dynamic queries on the same temp table, where one of the queries holds one record and the other trawls through related records so that I can essentially amalgamate the records into one.

DEFINE VARIABLE lv-SortField       AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-Query           AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-QueryString     AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-QueryBuffer     AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-Handle          AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-RecQueryString  AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-RecQueryHandle  AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-RecBufferHandle AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-i               AS INTEGER   NO-UNDO.

RUN GetBufferHandle IN lv-Browse-Manager-Handle
  (OUTPUT lv-Handle).

ASSIGN 
  lv-SortField = com-Unique:SCREEN-VALUE IN FRAME {&FRAME-NAME}.

ASSIGN 
  lv-QueryString = "FOR EACH " + lv-Handle:NAME + " BREAK BY " + lv-Handle:NAME + "." + lv-SortField.
  
CREATE QUERY lv-Query. 
lv-Query:SET-BUFFERS(lv-Handle).
lv-Query:QUERY-PREPARE(lv-QueryString).
lv-Query:QUERY-OPEN. 

lv-Query:GET-FIRST. 

lv-QueryBuffer = lv-Query:GET-BUFFER-HANDLE(1). 

DO WHILE NOT lv-Query:QUERY-OFF-END:
  IF lv-Query:FIRST-OF(1) THEN 
  DO:
    ASSIGN lv-RecQueryString = "FOR EACH " + lv-QueryBuffer:NAME + " WHERE RECID(" + lv-QueryBuffer:NAME + ") EQ " + STRING(lv-QueryBuffer:RECID). 
    CREATE QUERY lv-RecQueryHandle. 
    lv-RecQueryHandle:SET-BUFFERS(lv-QueryBuffer).
    lv-RecQueryHandle:QUERY-PREPARE(lv-RecQueryString).
    lv-RecQueryHandle:QUERY-OPEN. 
    lv-RecQueryHandle:GET-FIRST. 
    lv-RecBufferHandle = lv-RecQueryHandle:GET-BUFFER-HANDLE(1). 
  END. 
  ELSE 
  DO:
    MESSAGE "**JP**" lv-RecBufferHandle:BUFFER-FIELD(12):BUFFER-VALUE SKIP lv-QueryBuffer:BUFFER-FIELD(12):BUFFER-VALUE
      VIEW-AS ALERT-BOX.
    DO lv-i = 1 TO NUM-ENTRIES({&AmalgListStartDate}):
      FIND FIRST tt-DataCombos 
        WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListStartDate}) NO-ERROR. 
      IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN 
      DO:
        IF lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE LT lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE THEN 
          lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
      END. 
    END. 
    DO lv-i = 1 TO NUM-ENTRIES({&AmalgListEndDate}):
      FIND FIRST tt-DataCombos 
        WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListEndDate}) NO-ERROR. 
      IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN 
      DO:
        IF lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE GT lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE THEN 
          lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
      END. 
    END. 
    DO lv-i = 1 TO NUM-ENTRIES({&AmalgListDec}):
      FIND FIRST tt-DataCombos 
        WHERE tt-DataCombos.DataColumn EQ entry(lv-i,{&AmalgListDec}) NO-ERROR. 
      IF tt-DataCombos.WidgetHandle:SCREEN-VALUE NE "0" THEN 
        lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE = lv-RecBufferHandle:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE + lv-QueryBuffer:BUFFER-FIELD(tt-DataCombos.BrowseColumn):BUFFER-VALUE.
    END. 
    lv-QueryBuffer:BUFFER-DELETE. 
  END. 
  
  IF lv-Query:LAST-OF(1) THEN 
  DO:
    IF VALID-HANDLE(lv-RecQueryHandle) THEN 
    DO:
      lv-RecQueryHandle:QUERY-CLOSE. 
      DELETE OBJECT lv-RecQueryHandle.
    END. 
  END.  
  lv-Query:GET-NEXT. 
END. 

lv-Query:QUERY-CLOSE. 
DELETE OBJECT lv-Query.

The trouble is that both the buffer handles contain the same record. Is there a way of doing what I want to do?

 

[Stefan]

So you need to create an extra buffer. The create buffer statement also takes a buffer-handle as input.

 

[Cringer]

Duuuh of course. Thanks Stefan.