CREATE TABLE A
(
  ID Integer NOT NULL,
  O Varchar(20),
  CONSTRAINT PK_A__ID PRIMARY KEY (ID)
);
CREATE TABLE G
(
  ID Integer NOT NULL,
  C Timestamp,
  K Varchar(20),
  S Varchar(5),
  CONSTRAINT PK_G__ID PRIMARY KEY (ID),
  CONSTRAINT UK_G__K UNIQUE (K)
);
CREATE TABLE OT
(
  ID Integer NOT NULL,
  ID_A Integer,
  S Varchar(5),
  FLAG Integer,
  K Varchar(20),
  CONSTRAINT PK_OT__ID PRIMARY KEY (ID),
  CONSTRAINT UK_OT__K__S UNIQUE (K,S)
);
CREATE TABLE OTE
(
  ID Integer NOT NULL,
  ID_OT Integer NOT NULL,
  ID_A Integer NOT NULL,
  CZAS Timestamp,
  CONSTRAINT PK_OTE__ID PRIMARY KEY (ID),
  CONSTRAINT UK_OTE__ID_OT UNIQUE (ID_OT)
);



CREATE INDEX IXA_A__O ON A (O);
ALTER TABLE OT ADD CONSTRAINT FK_OT__ID_A
  FOREIGN KEY (ID_A) REFERENCES A (ID) ON UPDATE CASCADE ON DELETE NO ACTION;
CREATE INDEX IXA__OTE__ID_A ON OTE (ID_A);
CREATE DESCENDING INDEX IXD__OTE__CZAS ON OTE (CZAS);

/* ################################################################## */


INSERT INTO A (ID, O) VALUES ('0', '5555');
INSERT INTO A (ID, O) VALUES ('1', '6666');

INSERT INTO G (ID, C, K, S) VALUES ('0', '22.01.1989, 13:49:18.576', '6666', 'A');
INSERT INTO G (ID, C, K, S) VALUES ('1', '22.01.2001, 13:49:18.576', '5555', 'B');

INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('0', '1', 'B', NULL, '01');
INSERT INTO OT (ID, ID_A, S, FLAG, K) VALUES ('1', '0', 'A', '1993531', '02');

INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('0', '0', '0', '13.08.1980, 09:01:13.000');
INSERT INTO OTE (ID, ID_OT, ID_A, CZAS) VALUES ('1', '1', '1', '05.03.1987, 06:32:57.000');

################################################


SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=A.ID
INNER JOIN OTE OTE ON OT.ID=OTE.ID_OT
WHERE
( (OT.S<>'KO') AND (OT.FLAG IS NULL)
AND
COALESCE((SELECT MAX(AAA.CZAS) FROM (SELECT COALESCE((SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC), OTE.CZAS) AS CZAS_HG FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT OTE.CZAS AS HIST_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT (SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC) AS G_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID ) AS AAA(CZAS)), CAST('1900-01-01 12:00' AS TIMESTAMP)) >=CAST('2010-01-21 12:30:37' AS TIMESTAMP) ) ORDER BY A.O

#########################################

plan is ..
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN SORT (JOIN (AAA G INDEX (UK_G__K), AAA OST INDEX (UK_OT__K__S)))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (AAA OT_SUB INDEX (PK_OT__ID), AAA OTE INDEX (UK_OTE__ID_OT))
PLAN JOIN (A ORDER IXA_A__O, OT INDEX (FK_OT__ID_A), OTE INDEX (UK_OTE__ID_OT))

#############################################

i got error
SQL message: -508
the cursor identified in the update or delete statement is not positionet on a row.
engine code: 335544348
no curent row for fetch operation

#############################################

but when i change to use index on A (A.O='6666')

SELECT OT.*
FROM
OT OT
INNER JOIN A A ON OT.ID_A=A.ID
INNER JOIN OTE OTE ON OT.ID=OTE.ID_OT
WHERE
A.O='6666' AND
( (OT.S<>'KO') AND (OT.FLAG IS NULL)
AND
COALESCE((SELECT MAX(AAA.CZAS) FROM (SELECT COALESCE((SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC), OTE.CZAS) AS CZAS_HG FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT OTE.CZAS AS HIST_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID UNION SELECT (SELECT FIRST 1 G.C FROM G G INNER JOIN OT OST ON G.K=OST.K AND G.S=OST.S WHERE G.K=OT_SUB.K ORDER BY G.C DESC) AS G_CZAS FROM OT OT_SUB INNER JOIN OTE OTE ON OT_SUB.ID=OTE.ID_OT WHERE OT_SUB.ID=OT.ID ) AS AAA(CZAS)), CAST('1900-01-01 12:00' AS TIMESTAMP)) >=CAST('1977-01-21 12:30:37' AS TIMESTAMP) ) ORDER BY A.O

PLAN JOIN (A ORDER IXA_A__O INDEX (IXA_A__O), OT INDEX (FK_OT__ID_A), OTE INDEX (UK_OTE__ID_OT))

then this work