My oh my index.....

Y

yoachan

Member
Have you ever notice this? I'll try to explain it in code:

Code: 
DEF TEMP-TABLE tmp_table
    FIELD tmp_a as char
    FIELD tmp_b as int
    INDEX tmp_idx1 is primary unique tmp_a
    INDEX tmp_idx2 tmp_a tmp_b.

CREATE tmp_table.
SET tmp_a = "A"
    tmp_b = 1.


MESSAGE "LETS START CONFUSING LOOP" VIEW-AS ALERT-BOX.
FOR EACH tmp_table WHERE tmp_a = "A" USE-INDEX tmp_idx2.
    SET tmp_b = tmp_b + 1.
    MESSAGE "THIS IS OLD" tmp_b.
    IF (tmp_b = 10) THEN LEAVE.
end.

MESSAGE "LETS START OUR REAL LOOP" VIEW-AS ALERT-BOX.
FOR EACH tmp_table WHERE tmp_a = "A" USE-INDEX tmp_idx2.
    MESSAGE "THIS IS NEW" tmp_b.
END.
without the IF (tmp_b = 10) THEN LEAVE. PROGRESS will run forever though I only have one record.....
I'm stuck for hours because of this PROGRESS behavior. I don't know about other DBMS.
I just want to share to everyone, just be very carefull with your index...
Doing things like above make me feels silly because I should've change my Index value... :(
Though I only wanted progress to returns one record, I will get unlimited loop for the result...
 
yoachan said:
Have you ever notice this? I'll try to explain it in code:

Code: 
DEF TEMP-TABLE tmp_table
    FIELD tmp_a as char
    FIELD tmp_b as int
    INDEX tmp_idx1 is primary unique tmp_a
    INDEX tmp_idx2 tmp_a tmp_b.
 
CREATE tmp_table.
SET tmp_a = "A"
    tmp_b = 1.
 
 
MESSAGE "LETS START CONFUSING LOOP" VIEW-AS ALERT-BOX.
FOR EACH tmp_table WHERE tmp_a = "A" USE-INDEX tmp_idx2.
    SET tmp_b = tmp_b + 1.
    MESSAGE "THIS IS OLD" tmp_b.
    IF (tmp_b = 10) THEN LEAVE.
end.
 
MESSAGE "LETS START OUR REAL LOOP" VIEW-AS ALERT-BOX.
FOR EACH tmp_table WHERE tmp_a = "A" USE-INDEX tmp_idx2.
    MESSAGE "THIS IS NEW" tmp_b.
END.
without the IF (tmp_b = 10) THEN LEAVE. PROGRESS will run forever though I only have one record.....
I'm stuck for hours because of this PROGRESS behavior. I don't know about other DBMS.
I just want to share to everyone, just be very carefull with your index...
Doing things like above make me feels silly because I should've change my Index value... :(
Though I only wanted progress to returns one record, I will get unlimited loop for the result...
Click to expand...

You do realise that the second index is totally redundant as the primary index on tmp_a is unique:confused: you shouldn't really be using use-index other than in very rare cases.

Just read the code more closely, you are forcing to use index 2 which has field tmp_b in it and you are incrementing it so you will continually refind it on every itteration of the loop.
If you remove the use-index it will work correctly and will have incremented tmp_b by one which is what you appear to be wanting to do.
 
I came across this behaviour the other day when trying to fix a data error on a customer site. Imagine my consternation when 10 records seemingly ballooned to thousands before I terminated the procedure. It eventually dawned on me what was happening and I was able to sort it. My solution was to start at the end of the data and work forwards, incrementing the values. That way you will never refind a 'fixed' record.
 
Do you not have access to the documentation?

If you use the PRESELECT option with a DO or REPEAT block, the 4GL creates an internal list of the records selected. The PRESELECT option tells the 4GL to apply that internal list to the buffer you define.
Click to expand...
 
You must log in or register to reply here.
Back
Top