I am using Delphi 10.3 and ZEOS 7.2.14 to access an Oracle Database.
One of my queries is only returning 1 record while the same query returns a lot of rows when run in Oracle SQL Developer.
I have so far narrowed down the problem to a JOIN using a subquery:
SELECT
zp.MELDEPUNKT
FROM TT_AP_MSCONS_RLM zp
LEFT OUTER JOIN TT_AP_MSCONS_RLM lv
ON (lv.MELDEPUNKT = zp.MELDEPUNKT)
Returns many rows. Using a trivial subquery in the JOIN:
SELECT
zp.MELDEPUNKT
FROM TT_AP_MSCONS_RLM zp
LEFT OUTER JOIN (
SELECT
MELDEPUNKT
FROM TT_AP_MSCONS_RLM
) lv
ON (lv.MELDEPUNKT = zp.MELDEPUNKT)
Returns the same number of records. Adding a GROUP BY to the subquery:
SELECT
zp.MELDEPUNKT
FROM TT_AP_MSCONS_RLM zp
LEFT OUTER JOIN (
SELECT
MELDEPUNKT
FROM TT_AP_MSCONS_RLM
GROUP BY MELDEPUNKT
) lv
ON (lv.MELDEPUNKT = zp.MELDEPUNKT)
Still returns a lot of records. But when I add another field to the subquery like this:
SELECT
zp.MELDEPUNKT
FROM TT_AP_MSCONS_RLM zp
LEFT OUTER JOIN (
SELECT
MELDEPUNKT,
MAX(EMAIL_VERSAND_TIMESTAMP)
FROM TT_AP_MSCONS_RLM
GROUP BY MELDEPUNKT
) lv
ON (lv.MELDEPUNKT = zp.MELDEPUNKT)
Now suddenly it returns just a single row. Running this last query in Oracle SQL Developer returns the expected large number of rows.
What could make the queries work differently when executed using the ZEOS components and when run in SQL Developer?