You can translate the question and the replies:

Joining CTE using a LEFT JOIN in Postgres throw error

When using a left join to join a CTE in Postgres, we receive a column "does not exist" error. This error occurs when one or several of the columns in the CTE are not used in the joined query. When inspecting the Execution Trace and reviewing the Postgres dialect, the translation to Postgres is incorrect and throws an error. Example below: **Denodo query:** WITH trace AS ( SELECT id, name FROM trace WHERE id IS NOT NULL ) SELECT code, revision FROM tickets LEFT JOIN trace ON tickets.code = **ERROR** QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ERROR: column does not exist When inspecting the Postgres dialect translation, there is an error in the Postgres query that the VDP sends to the Postgres data source. The CTE does not have a "name" column and the LEFT JOIN subquery is calling that missing "name" column from the CTE, thus throwing a Postgres error. **Postgres Query** WITH w0 AS (SELECT t1."id" AS id FROM "public".trace t1 WHERE (t1."id") IS NOT NULL) SELECT t0."code" AS "Code", t0."revision" AS "Revision" FROM "public".tickets t0 LEFT JOIN (SELECT AS Id, AS Name FROM w0 s0 ) s1 ON ((t0."code" = s1.Id) ) LIMIT 150 This seems like a bug. Can this be confirmed? Any help or workarounds would be appreciated.
15-11-2022 13:27:19 -0500

1 Answer

Hi, I tried this query with some dummy tables and data on my own installation but it executed successfully. Since I was unable to reproduce this error, I would recommend ensuring that you are on the latest version of VDP (Virtual DataPort 8.0 20220815 or Virtual DataPort 7.0 20220531 depending on the version of your installation) and trying again. There is a user manual for [Installing Updates and Hotfixes]( for more information on updating. If you're still getting the same error on the latest VDP version, I would recommend submitting a support case if you have valid support user. They'll be able to investigate the various logs and execution traces to see what's going on. Hope this helps!
Denodo Team
16-11-2022 18:48:56 -0500
You must sign in to add an answer. If you do not have an account, you can register here