Delphi and ZEOS access components returning just a single record from Oracle database

231 views Asked by At

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?

0

There are 0 answers