Upon using the custom query in the source object, the mapping fails with the following error:
The select query for read operation failed because of the following error: [org.postgresql.util.PSQLException: Multiple ResultSets were returned by the query
However, the same query works as expected in PostGresDB.
If the custom query has more than two query statements, one which is commented and separated by a semicolon, then multiple ResultSets were returned by the query.
This is expected behavior as the JDBC driver verifies both the statement and returns a null value for the blank query which is a commented line.
Solution:
To resolve the issue, either remove the commented lines from the query or keep the commented lines at the top of the query, so it can be disregarded while running the original query