dynamic break-by work around????

D

dayv2005

Member
Ok im running version 9.1d and dynamic queries don't support dynamic break-bys. I don't even really need a dynamic break by either. I just need to use a break by in a dynamic query.

Here's my code and i was wondering if anyone out there knows how i can achieve the break by in this? THanks...

Code: 
DEFINE VARIABLE HiredCT     AS INTEGER         NO-UNDO.
    DEFINE VARIABLE hQuery      AS HANDLE          NO-UNDO.
    DEFINE VARIABLE PaperTypeCT AS INTEGER         NO-UNDO.
    DEFINE VARIABLE qh          AS WIDGET-HANDLE   NO-UNDO.
    DEFINE VARIABLE qString     AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE sQuery      AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE sSort       AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE cBreakBy    AS CHARACTER       NO-UNDO.

    ASSIGN
        sSort    = " BY JobApplicant.SpecificNewspaper "
        cBreakBy = "JobApplicant.HowDidyouHearofUs".

    CASE cb-type:SCREEN-VALUE:
        WHEN "All" THEN
            sQuery = "".
        OTHERWISE
            sQuery = " And JobApplicant.ApplicantType = " + "~"" + cb-Type:SCREEN-VALUE + "~"".
    END CASE.
              
    qString = SUBSTITUTE("For each jobapplicant no-lock where jobapplicant.ApplicationDate >= " + scr-FromDate:SCREEN-VALUE + " and " +
                         "JobApplicant.ApplicationDate <= " + scr-ToDate:SCREEN-VALUE + 
                         " &1 NO-LOCK &2 INDEXED-REPOSITION", sQuery, sSort).

    CREATE QUERY qh.
    qh:SET-BUFFERS(BUFFER JobApplicant:HANDLE).
    qh:QUERY-PREPARE(qString).
    qh:QUERY-OPEN.

    REPEAT:
        qh:GET-NEXT().
        IF qh:QUERY-OFF-END THEN LEAVE.

        /*BREAK BY JobApplicant.HowDidyouHearofUs.*/

        PaperTypeCT = PaperTypeCT + 1.
                
        IF JobApplicant.StatusType = "Hired" THEN
            HiredCT = HiredCT + 1.
    
        /*IF LAST-OF(JobApplicant.SpecificNewspaper) THEN*/
        DO:
            CREATE rep-NewsPapers.
            ASSIGN 
                rep-NewsPapers.NewsPaperName    = IF JobApplicant.SpecificNewspaper = "" OR JobApplicant.SpecificNewspaper = ? THEN "[Unspecified]" ELSE JobApplicant.SpecificNewspaper
                rep-NewsPapers.Source           = trim(JobApplicant.HowDidyouHearofUs)
                rep-NewsPapers.ReportID         = tReportID
                rep-NewsPapers.Applicants       = PaperTypeCT
                rep-NewsPapers.Hired            = HiredCT.

    
            ASSIGN
                PaperTypeCT = 0
                HiredCT = 0.
                 
        END.
    END.

    qh:QUERY-CLOSE().
    DELETE OBJECT qh.

    ASSIGN
        BuildFilter  = "rep-NewsPapers.ReportID = ~"" + tReportID + "~""
        BuildParams  = "~nDisplayDate = " + scr-FromDate:SCREEN-VALUE + " TO " + scr-toDate:SCREEN-VALUE +
                       " ~n Heading = " + cb-type:SCREEN-VALUE.
 
Would this work?

Code: 
    DEFINE VARIABLE HiredCT     AS INTEGER         NO-UNDO.
    DEFINE VARIABLE hQuery      AS HANDLE          NO-UNDO.
    DEFINE VARIABLE PaperTypeCT AS INTEGER         NO-UNDO.
    DEFINE VARIABLE qh          AS WIDGET-HANDLE   NO-UNDO.
    DEFINE VARIABLE qString     AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE sQuery      AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE sSort       AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE cBreakBy    AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE cFieldHold  AS CHARACTER       NO-UNDO.
    DEFINE VARIABLE isLast      AS LOGICAL         NO-UNDO.

    ASSIGN
        sSort      = " BY JobApplicant.SpecificNewspaper "
        cBreakBy   = "JobApplicant.SpecificNewspaper"
        cFieldHold = ""
        isLast     = FALSE.

    CASE cb-type:SCREEN-VALUE:
        WHEN "All" THEN
            sQuery = "".
        OTHERWISE
            sQuery = " And JobApplicant.ApplicantType = " + "~"" + cb-Type:SCREEN-VALUE + "~"".
    END CASE.

    qString = SUBSTITUTE("For each jobapplicant no-lock where jobapplicant.ApplicationDate >= " + scr-FromDate:SCREEN-VALUE + " and " +
                         "JobApplicant.ApplicationDate <= " + scr-ToDate:SCREEN-VALUE + 
                         " &1 NO-LOCK &3 &2 INDEXED-REPOSITION", sQuery, sSort, cBreakBy).

    CREATE QUERY qh.
    qh:SET-BUFFERS(BUFFER JobApplicant:HANDLE).
    qh:QUERY-PREPARE(qString).
    qh:QUERY-OPEN.

    REPEAT:
        qh:GET-NEXT().
        IF qh:QUERY-OFF-END THEN LEAVE.
        
        cFieldHold = jobapplicant.SpecificNewspaper.

        qh:GET-NEXT().
        IF cFieldHold = jobapplicant.SpecificNewspaper THEN
        DO:
            qh:GET-PREV().
            PaperTypeCT = PaperTypeCT + 1.
                
            IF JobApplicant.StatusType = "Hired" THEN
                HiredCT = HiredCT + 1.
        END.
        ELSE
        DO:
            qh:GET-PREV().

            CREATE rep-NewsPapers.
            ASSIGN 
                rep-NewsPapers.NewsPaperName    = IF JobApplicant.SpecificNewspaper = "" OR JobApplicant.SpecificNewspaper = ? THEN "[Unspecified]" ELSE JobApplicant.SpecificNewspaper
                rep-NewsPapers.Source           = trim(JobApplicant.HowDidyouHearofUs)
                rep-NewsPapers.ReportID         = tReportID
                rep-NewsPapers.Applicants       = PaperTypeCT
                rep-NewsPapers.Hired            = HiredCT.

    
            ASSIGN
                PaperTypeCT = 0
                HiredCT = 0.
                 
        END.
    END.

    qh:QUERY-CLOSE().
    DELETE OBJECT qh.

    ASSIGN
        BuildFilter  = "rep-NewsPapers.ReportID = ~"" + tReportID + "~""
        BuildParams  = "~nDisplayDate = " + scr-FromDate:SCREEN-VALUE + " TO " + scr-toDate:SCREEN-VALUE +
                       " ~n Heading = " + cb-type:SCREEN-VALUE.
 
All a BREAK-BY is, is a simple way to test for a change in value. So, in your query processing use a variable to hold the value of the break-by field in the prior record and test it against the current record. If they are different, it is a new break group. Set the variable to an improbable high value to start so that you break on the first record and make sure that you set the variable after each record. Nothing fancy required.
 
If you are using something similar to break-by then you will need to sort the query first.

Either use USE-INDEX or BY in the query.

In your example, you don't need BY twice - so cut out cSort and it should work OK.

If you are not sure of how a query will work, just message the string used in the QUERY-PREPARE, then you can check that it looks OK.

Alternatively, don't sort the original query and apply your sort to the rep-NewsPapers table, which I assume is a reporting temp-table. That way you can be as clever as you like by defining sorting indexes on the temp-table.
 
You must log in or register to reply here.
Back
Top