Efficient Query?

[Bennie H.]

This is the situation:

I have a fill-in fiA, three tables, X, Y and Z, all 1000 records and a browse with columns, clA, clB, clC, clD, clE and clF.
Now I have to search in table X all the records with field Xa = fiA:screen-value.
After this search I have the values of columns clA, clB, clC and (clD or ClE, exclusive or) of my browse. When I have a value in column clD I have to read a matching record in table Y and can fill clF, when I have a value in column clE I have to read the matching record in table Z to fill column clF. What’s the most efficient way to do this?

Now I do first a ‘for each’ comparing table X and table Y on clD, put the results in a temp-table, do a ‘for each’ on table X comparing table Z on clE, append the results to the temp-table, order the temp-table on column clA DESC and put everything into a browse.
Is there a more efficient way to do this?

Thanks for your suggestions.

 

[bendaluz2]

This should do it (assuming your using a Smart Browse - If not, just ignore the local-display-fields bit)

<b><i>In Definitions</i></b>
<pre>
DEFINE VARIABLE c$lF AS CHARACTER NO-UNDO.
</pre>
<b><i>OPEN_QUERY OF br_table</i></b>
<pre>
OPEN QUERY {&SELF-NAME}
FOR EACH X
WHERE X.Xa = fiA:SCREEN-VALUE
NO-LOCK,
FIRST Y
WHERE Y.clD = X.clD
OUTER-JOIN NO-LOCK,
FIRST Z
WHERE Z.clE = X.clE
OUTER-JOIN NO-LOCK.
</pre>
<b><i>DISPLAY OF br_table</i></b>
<pre>
X.clA
X.clB
X.clC
X.clD
X.clE
c$lF
</pre>
<b><i>ROW-DISPLAY OF br_table</i></b>
<pre>
IF AVAILABLE Y THEN
ASSIGN c$lF = Y.lF.
ELSE
IF AVAILABLE Z THEN
ASSIGN c$lF = Z.lF.
ELSE
ASSIGN c$lF = "".
</pre>
<b><i>local-display-fields</i></b>
<pre>
/* RUN dispatch IN THIS-PROCEDURE ( INPUT 'display-fields':U ) . */
</pre>

 

[Bennie H.]

Thanks, I'm gonna give it a try...