The ERXExistsQualifier currently doesn't seem to work when multiple tables are referenced in the 'exists' condition. I think a few people are already aware of this but could anyone tell be if they know a way to work around this ? Because the only alternatives I see are either redesigning tables in my database or trying to correct the ERXExistsQualifier.class myself. Thanks in advance for any advice!
Here's the SQL output of two examples explaining why one works and not the other:
1) Example 1 - where the exists condition only refers to one table
(works because OBS table is correctly aliased exists0)
SELECT t0.*
FROM PTF t0
WHERE ( EXISTS ( SELECT exists0.ID
FROM OBS exists0
WHERE exists0.OBS_DATE BETWEEN date1 AND date2
AND exists0.PTF_ID = t0.ID
)
)
2) Example 2 - where the exists condition refers to multiple tables
(doesn't work because OBS table is aliased t0 instead of exists0, ...and the PTF_LOC table shouldn't be aliased T1 either)
SELECT t0.*
FROM PTF t0
WHERE ( EXISTS ( SELECT exists0.ID
FROM OBS t0, PTF_LOC T1
WHERE T1.LAT BETWEEN -40 AND 91
AND exists0.PTF_LOC_ID =
T1.ID AND exists0.PTF_ID = t0.ID
)
)
Next exception:SQL State:42000 -- error code: 904 -- msg: ORA-00904: "EXISTS0"."PTF_ID" : identificateur non valide
...And here's the 2 equivalent queries but with the IN qualifier(same problem):
1bis) SELECT t0.*
FROM PTF t0, PROGRAM T1, MASTER_PROG T2, PTF_STATUS T3
WHERE (t0.ID IN ( SELECT exists0.PTF_ID
FROM OBS exists0
WHERE exists0.OBS_DATE BETWEEN ? AND ? )
)
2bis) SELECT t0.*
FROM PTF t0
WHERE (t0.ID IN ( SELECT exists0.PTF_ID
FROM OBS t0, PTF_LOC T1
WHERE T1.LAT BETWEEN ? AND ?
AND exists0.PTF_LOC_ID =
T1.ID )
)