Dynamic Query BREAK BY

[jdpjamesp]

Hi all,

Hope I can explain this. I'm on 10.2A GUI on Windows for what it's worth.

I have a dynamic temp table that is created from an Excel sheet the user defines. As part of the load of the sheet, the user is asked to define what columns determines a record as each record spans multiple lines of the file. For argument's sake let's say a record is columns "Invoice Number,Invoice Date,Supplier Ref". Of course this could vary. I can build a dynamic query with a BREAK BY on these columns easily. The problem is that I'm not sure how to deal with the FIRST-OF side of things. I want to do
IF FIRST-OF(1) AND FIRST-OF(2) AND FIRST-OF(3) THEN ...
but what if there are only 2 columns that define a record? Or 20? Can anyone think of a clever way of dealing with this?

Thanks

 

[jdpjamesp]

Think I've found a solution that will work...

DEFINE VARIABLE lv-QueryString AS CHARACTER NO-UNDO.
DEFINE VARIABLE lv-Query       AS HANDLE    NO-UNDO.
DEFINE VARIABLE lv-i           AS INTEGER   NO-UNDO.
DEFINE VARIABLE lv-j           AS INTEGER   NO-UNDO.
DEFINE VARIABLE lv-First       AS LOGICAL   NO-UNDO.

lv-QueryString = "FOR EACH " + lv-DefaultBufferHandle:NAME + " ". 

DO lv-i = 1 TO NUM-ENTRIES(lv-Uniqueness):
  FIND FIRST tt-WSLabel 
    WHERE tt-WSLabel.SearchString EQ ENTRY(lv-i,lv-Uniqueness) NO-ERROR. 
  lv-QueryString =  lv-QueryString + (IF lv-i EQ 1 THEN "BREAK BY " ELSE "BY ") + 
                    lv-DefaultBufferHandle:NAME + "." + (IF AVAILABLE tt-WSLabel THEN tt-WSLabel.SflDescId ELSE ENTRY(lv-i,lv-Uniqueness)) + " ".
END. 

CREATE QUERY lv-Query. 
lv-Query:ADD-BUFFER(lv-DefaultBufferHandle).
lv-Query:QUERY-PREPARE(lv-QueryString).
lv-Query:QUERY-OPEN.

lv-Query:GET-FIRST.

DO WHILE NOT lv-Query:QUERY-OFF-END:
  DO lv-j = 0 TO lv-i - 1:
    lv-First = lv-Query:FIRST-OF(lv-j).
  END. 
  
  
      
  
  lv-Query:GET-NEXT.
END. 

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