Hi Kjell;

I am trying now to test all your ideas, thank you a lot!. But, I
think you will understand a little more my ... "poor" sql if I answer
your question "why the subquery for PROC.PROC in...?".

I do that way, because I have a big query system over a main table
called PROC, that has lots of one to many relations with other tables.

The user will combine anithing something like 200 filters for
building the where clause that filters PROC. Then, they can combine
lots of fields for guetting the resulting subset; and they also can
combine lots of index orders.

So I have no chance to write the best SQL for each of the possible
big number of combinations. Doing "PROC" in ... I found a clean way.

Now, all the combinations works at a nice speed. The problem becomes
when I have to combine that MOVI columns (for retrieving them, or for
filtering them, or for making the final indexed order).

So I think the most important improvement I can do, is finding a way
to optimizing that max(FECH) area of my query.

BTW, your first idea finished working yet, the resulting subset is
OK, but it taked 11 minutes:

Starting transaction...
Preparing query: select "PROC"."PROC",
"PROC"."CARP",
"PROC"."ACTO",
"PROC"."DEMA",
"PROC"."OBSE",
"TPRO"."DSCR" "D_TPRO",
m."FECH",
m."HORA",
m."DSCR" "D_MOVI"
from "PROC"
left outer join "TPRO" on "PROC"."TPRO" = "TPRO"."TPRO"
left outer join "MOVI" m
on "PROC"."PROC" = m."PROC"
and m."TIPO" = '1'
and m."HECH" = 'P'
where "PROC"."PROC" in (
select "PROC"
from "PROC"
left outer join "OJUD" on "PROC"."OJUD" = "OJUD"."OJUD"
left outer join "ZONA" on "OJUD"."ZONA" = "ZONA"."ZONA"
where ("ZONA"."ORDE" between '000000550' and '000000550')
)
and (m."PROC" is null
or m."FECH" = (
select max(msub."FECH")
from "MOVI" msub
where msub."PROC" = m."PROC"
and msub."TIPO" = '1'
and msub."HECH" = 'P'
))
order by lower("PROC"."ACTO"),
"PROC"."PROC"

Prepare time: 00:00:01.
Field #01: PROC.PROC Alias:PROC Type:STRING(10)
Field #02: PROC.CARP Alias:CARP Type:STRING(18)
Field #03: PROC.ACTO Alias:ACTO Type:STRING(200)
Field #04: PROC.DEMA Alias:DEMA Type:STRING(200)
Field #05: PROC.OBSE Alias:OBSE Type:STRING(120)
Field #06: TPRO.DSCR Alias:D_TPRO Type:STRING(120)
Field #07: MOVI.FECH Alias:FECH Type:STRING(8)
Field #08: MOVI.HORA Alias:HORA Type:STRING(4)
Field #09: MOVI.DSCR Alias:D_MOVI Type:STRING(200)
PLAN JOIN (JOIN (PROC INDEX (PROC_PK), OJUD INDEX (OJUD_PK)), ZONA
INDEX (ZONA_PK))
PLAN (MSUB INDEX (MOVI_PROC))
PLAN JOIN (SORT (JOIN (PROC NATURAL, TPRO INDEX (TPRO_PK))), M INDEX
(MOVI_PROC))

Executing...
Done.
6103572 fetches, 12 marks, 509204 reads, 12 writes.
0 inserts, 0 updates, 0 deletes, 2024146 index, 23127 seq.
Delta memory: 107656 bytes.
Execute time: 00:11:22.
Script execution finished.


I hope you understand me now. Thank you.

Dany