Queries and Browser

M

MauditOstie

Member
Hi everyone, i recently changed from CHUI to GUI and well,
i'm having a hard time loading my queries properly.

Thing is, there are 2 tables linked.
Employer and Emplr-Status.

Here is what i would like it to do, in simple CHUI code:
Code: 
FOR EACH employer NO-LOCK:
   FIND LAST emplr-status where emplr-status.emplr-code = Employer.emplr-code AND
           emplr-Status.effective-date <= TODAY No-lock No-error.
  
    IF emplr-status.status-code = 5 THEN
       DISPLAY emplr-status.
END.

All i want in my browser are emplr-status.status-code = 5.
I cannot find a way to create a query that will work :(

Example:

Employer No: 123456

Emplr-status records:

Seq Emplr No Effective Date Status
-------------------------------------------------------------
1 123456 01/01/2010 1
2 123456 02/02/2010 2
3 123456 04/05/2010 6
4 123456 04/05/2010 5
5 123456 04/05/2010 2
6 123456 12/12/2010 5

The CHUI code will position us on Seq #5 of the status...

But all i was able to do right now is positioning my query on Seq #4 so it wrongly shows in my broswer...

Anyone got an idea? Here is an example that i tried and that failed. I also tried it with a buffer on my emplr-status table to check the rowid's. I'm really running out of ideas.

EXAMPLE 1:
Code: 
&Scoped-define QUERY-STRING-br_table FOR EACH employer WHERE ~{&KEY-PHRASE} NO-LOCK, ~
                                                            LAST emplr-status WHERE emplr-status.emplr-code = employer.emplr-code ~
                                                             AND emplr-status.effective-date LE TODAY AND ~
                                                             (IF emplr-status.status-code EQ 5 THEN TRUE ELSE FALSE) NO-LOCK ~
                                                            {&SORTBY-PHRASE} INDEXED-REPOSITION

&Scoped-define OPEN-QUERY-br_table OPEN QUERY br_table FOR EACH employer WHERE ~
                                                                     {&KEY-HRASE} NO-LOCK, ~
                                                          LAST emplr-status WHERE emplr-status.emplr-code = employer.emplr-code ~
                                                           AND emplr-status.effective-date LE TODAY AND ~
                                                           (IF emplr-status.status-code EQ 5 THEN TRUE ELSE FALSE) NO-LOCK ~
                                                        {&SORTBY-PHRASE} INDEXED-REPOSITION.
 
Hi there.

I might be missing something, but my interpretation of your CHUI code means that the Emplr-status row seq #4 would be shown, not #5. #4 is the last record that matches both the date condition and the status condition. (Assuming the default index on Emplr-status includes the date field)

In translating this into a query condition, I don't understand why you have changed "emplr-status.status-code = 5" into "emplr-status.status-code LE 4". The original condition should have worked just fine.


So my suggestion for converting your original FOR loop into an OPEN QUERY statement would look like this:

Code: 
OPEN QUERY br_table 
   FOR EACH employer 
      WHERE {&KEY-PHRASE} NO-LOCK,
   LAST emplr-status 
      WHERE emplr-status.emplr-code = employer.emplr-code
        AND emplr-status.effective-date LE TODAY 
        AND emplr-status.status-code = 5
      NO-LOCK.
 
Hi There, you are right, i should have re-read my post. i Fixed my Query as this was from the wrong program. Was program shows status 5, the other one shows all status lower then 5.

Thing is, all i want is all current and most recent record, and if it's status 5 then display it.

Code: 
FOR EACH employer NO-LOCK:
FIND LAST emplr-status where emplr-status.emplr-code = Employer.emplr-code AND
                emplr-Status.effective-date <= TODAY No-lock No-error.
IF emplr-status.status-code = 5 THEN
   DISPLAY emplr-status.
END.

This will position itself on the #5 Sequence and check if the status is 5. If so, then we can display it.
The reason why i played with the status is that the users are allowed to change the status as much as they want and this in the same day. That's the reason i have to use the Effective-Date and the sequence (primary index is by date and sequence ASC.
 
Ok i am now able to get the data i want.
Infortunatly, this is not fast at all in a Broswer.
Here is what i did:

Code: 
DEFINE BUFFER bnst FOR status.

OPEN QUERY br_table 
   FOR EACH employer 
      WHERE {&KEY-PHRASE} NO-LOCK,
   LAST emplr-status 
      WHERE emplr-status.emplr-code = employer.emplr-code
        AND emplr-status.effective-date LE TODAY,
 FIRST bnst WHERE
          RECID(emplr-status) = RECID(bnst) AND
          bnst.status = 5
      NO-LOCK.

This gets me my record, but my searches using this is AWFUL! 200 000 records takes about 3 minutes.

Code: 
    FIND FIRST employer WHERE employer.emplr-code GE pcsearch 
     USE-INDEX i-emplr-code NO-LOCK NO-ERROR.
     IF AVAIL(employer) THEN DO:
        FIND LAST emplr-status 
             WHERE emplr-status.emplr-code EQ employer.emplr-code
                  AND emplr-status.effective-date LE TODAY 
            NO-LOCK NO-ERROR.
        IF AVAIL emplr-status AND 
           emplr-status.status-code EQ 5 THEN
           RELEASE employer.
     END.

/**** Up until here, the record is found within 1 sec ****/

IF AVAIL employer THEN DO:
   REPOSITION br_table TO ROWID ROWID(employer) NO-ERROR.
   APPLY "VALUE-CHANGED":U TO BROWSE br_table.
END.

/**** The reposition takes minimum 3 minutes ****/
 
That's a shame, but I'm not surprised that loading 200 000 records into a browse takes that long. I've recently been dealing with similar speed problems, and found the best way was to add a filter with easy to use controls so the user can quickly filter down to what they're looking for.
 
You must log in or register to reply here.
Back
Top