Error 4856 on CTE

Goldengate

Hi fellow Vertica users!! I've a problem with this CTE running on Dbeaver I get this error:

SQL Error [4566] [42V01]: [Vertica]VJDBC ERROR: Relation "actualiza" does not exist

With this SQL which I've tested on SMSS and is running fine, just counting numbers nothing fancy:

WITH actualiza AS
(
SELECT 0 AS numero

UNION ALL

SELECT numero + 1
FROM actualiza AS cuenta
WHERE cuenta.numero < 10

) SELECT * FROM actualiza

Don't know what I'm doing wrong, by the way I'm a newbie on Vertica and trying to learn it.

Best regards!!!

Frank

s_crossman

Hi Frank,

I tested this with Vertica 10.1.1 and DBeaver 21.3.0. Seems to work. I created a simple table with the one column and then ran the CTE in VSQL to verify Vertica could process it, and then ran in DBeaver as well as DBVisualizer. Returned the expected single numero col and row with value 0 table.

The error indicates it can't find the actualiza table. This could be DBeaver pointing to a different db that doesn't have the table, or possibly the user you are connecting to the db with doesn't have access to the actualiza table. I would check those two things first. An easy way to eliminate this being the CTE is to open the DBeaver sql console and issue a select count against the table (e.g. WITH actualiza AS ( SELECT 0 AS numero UNION ALL SELECT numero + 1 FROM actualiza AS cuenta WHERE cuenta.numero < 10 ) SELECT * FROM actualiza). If it gives same relation not found then it's definitely one of the two items above. if it returns a count then double check the definition of the table to your CTE names used (numero and actualiza).

One other thought. If the actualiza table is not under the public schema then you may have to fully qualify the schema.tablename.
e.g.
select count(*) from someschemaname.actualiza

I hope it helps,

s_crossman

Hi Frank,

I tested this with Vertica 10.1.1 and DBeaver 21.3.0. Seems to work. I created a simple table with the one column and then ran the CTE in VSQL to verify Vertica could process it, and then ran in DBeaver as well as DBVisualizer. Returned the expected single numero col and row with value 0 table.

The error indicates it can't find the actualiza table. This could be DBeaver pointing to a different db that doesn't have the table, or possibly the user you are connecting to the db with doesn't have access to the actualiza table. I would check those two things first. An easy way to eliminate this being the CTE is to open the DBeaver sql console and issue a select count against the table (e.g. SELECT count(*) FROM actualiza). If it gives same relation not found then it's definitely one of the two items above. if it returns a count then double check the definition of the table to your CTE names used (numero and actualiza).

One other thought. If the actualiza table is not under the public schema then you may have to fully qualify the schema.tablename.
e.g.
select count(*) from someschemaname.actualiza

I hope it helps,