Pre-Proc

[MauditOstie]

Hi everyone,

We have an existing platforme that has a very slow functionnality. I was easilly able to figure out what was the problem but unfortunatly i can't seem to be able to fix it!

Here we go:

I have a lookup button on a window that brings a browser where you can search a record by clicking on a colon header and entering data in the searchkey field. This is working very nicely! I also have on that particular lookup screen a button that can be clicked, and will bring a window with the available "extra" search criteria. When i get back from that screen, the program re-open the query using those "extra" criteria. Unfortunatly, this messes up my bracketing!! like A LOT!!! On a 150 000 record table, it takes around 2 seconds (not bad). on a 2.2millions record table, it takes around 8minutes! (VERY bad). Bad thing having so many if's in a for each...

Here is how the guys here created the thing: He used a Browser with a standard Query and used the &SEARCH-KEY Pre-Proc as such

       &SCOPED-DEFINE KEY-PHRASE (IF isMatching[1] THEN (Nss-employee.Surname           MATCHES cValue[1]) ELSE (IF cValue[1] NE "":U THEN Nss-employee.Surname           EQ cValue[1]     ELSE TRUE)) AND ~
                                 (IF isMatching[2] THEN (Nss-Employee.First-name        MATCHES cValue[2]) ELSE (IF cValue[2] NE "":U THEN Nss-Employee.First-name        EQ cValue[2]     ELSE TRUE)) AND ~
                                 (IF DATE(cValue[3]) NE ? THEN Nss-Employee.Birth-Date EQ DATE(cValue[3])  ELSE TRUE)                                                                                  AND ~
                                 (IF isMatching[4] THEN (Nss-Employee.national-id       MATCHES cValue[4]) ELSE (IF cValue[4] NE "":U THEN Nss-Employee.national-id       EQ cValue[4]    ELSE TRUE))  AND ~
                                 (IF isMatching[5] THEN (Nss-Employee.Passport-No       MATCHES cValue[5]) ELSE (IF cValue[5] NE "":U THEN Nss-Employee.Passport-No       EQ cValue[5]    ELSE TRUE))  AND ~
                                 (IF isMatching[6] THEN (Nss-Employee.Birth-Certif-No   MATCHES cValue[6]) ELSE (IF cValue[6] NE "":U THEN Nss-Employee.Birth-Certif-No   EQ cValue[6]    ELSE TRUE))  AND ~
                                 (IF isMatching[7] THEN (Nss-Employee.Driver-Licence-No MATCHES cValue[7]) ELSE (IF cValue[7] NE "":U THEN Nss-Employee.Driver-Licence-No EQ cValue[7]    ELSE TRUE))  AND ~
                                 (IF isMatching[8] THEN (Nss-Employee.SSN               MATCHES cValue[8] AND nss-employee.Nss-Entity > "") ELSE (IF cValue[8] NE "":U THEN (Nss-Employee.SSN               EQ cValue[8] AND nss-employee.Nss-Entity > "" ) ELSE TRUE))  AND ~
                                 (IF gcIncomingTable BEGINS "Deleted" THEN(Nss-employee.Status-Code EQ  7) ELSE (Nss-employee.Status-Code NE 7))

INSANE! It works! but Outch!

Thing is, i tried the Dynamic Query way, but i have a hard time being able to re-display all my colums (some are functions such as GetDesc(status.status-code) @ desc) and the headers can't seem to be put in BOLD (this is our way of showing the user that he can click on that column and to a Search with it using the searchkey).

I also tried using Pre-Proc. I would assign and create my Query in a variable and then send it to an include to run. Here is an example with only 2 criteria but this screen has in reality 9 (First Name, Last Name, Social Number, Region, City etc...) :

         IF isMatching[1] THEN DO:
            IF cValue[1] NE "":U THEN DO:
                ASSIGN cWhereClause = 'Nss-employee.Surname MATCHES "' + cValue[1] + '"'
                        cAnd = " AND ":U
                        cIndex = " USE-INDEX Name".
            END.
         END.
         ELSE DO: 
            IF cValue[1] NE "":U THEN DO:
                  ASSIGN cWhereClause = 'Nss-employee.Surname EQ "' + cValue[1] + '"'
                         cAnd = " AND ":U
                         cIndex = " USE-INDEX Name".
            END.
         END.

         IF cValue[2] NE "":U THEN DO:
            IF isMatching[2] THEN DO:
               ASSIGN cWhereClause = cWhereClause + cAnd + 'Nss-Employee.First-name MATCHES "' + cValue[2] + '"'
                      cAnd = " AND ":U.
               IF cIndex = "":U THEN ASSIGN cIndex = " USE-INDEX Name".
            END.
            ELSE DO:
                   ASSIGN cWhereClause = cWhereClause + cAnd + 'Nss-Employee.First-name EQ "' + cValue[2] + '"'
                          cAnd = " AND ":U.
               IF cIndex = "":U THEN ASSIGN cIndex = " USE-INDEX Name".

            END.
         END.
         ASSIGN wQuery = "OPEN QUERY {&BROWSE-NAME} FOR EACH nss-employee WHERE " + cWhereClause + " "  + 'USE-INDEX first-name' + " NO-LOCK INDEXED-REPOSITION".
         MESSAGE wQuery
             VIEW-AS ALERT-BOX INFO BUTTONS OK.

{ns\maint\keyphrase.i wQuery} 

/******* Content of keyphrase.i **************/

&SCOPED-DEFINE QUERYTOOPEN {1}

MESSAGE cWhereClause SKIP
              cAnd SKIP
              cIndex
    VIEW-AS ALERT-BOX INFO BUTTONS OK TITLE "in the .i".

MESSAGE {1} SKIP
             '{1}' SKIP
             {&QUERYTOOPEN}
    VIEW-AS ALERT-BOX INFO BUTTONS OK TITLE "in the .i".

/**** This should execute the code ****/

{1}.
/* OR */
{&QUERYTOOPEN}.

/***** None are working! None are creating errors or nothing. **********/

This acted funny. Whenever i would MESSAGE the {1}, i would get:

MESSAGE {1} SKIP
             '{1}' SKIP
             {&QUERYTOOPEN}
    VIEW-AS ALERT-BOX INFO BUTTONS OK TITLE "in the .i".

So {1} shows OPEN QUERY blablabla....
and '{1}' shows "wQuery" and
{&QUERYTOOPEN} shows OPEN QUERY blablabla....

Therefore, in that include i tried putting {1} or {&QUERYTOOPEN} on the code so that it would execute the "OPEN QUERY" like you would typing it but it would not do a thing

Anyone knows how i can do this?? I want to be able to get my query re-open with the proper search items... those items can be filled or not.

 

[MaximMonin]

You cannot execute "string".
Instead use dynamic query.
sqlquery = FOR EACH nss-employee WHERE " + cWhereClause + " " + 'USE-INDEX first-name' + " NO-LOCK INDEXED-REPOSITION".
hQuery:QUERY-PREPARE(sqlquery).