SQL statement error

[slunah]

Hello, I am using this statement with the DB SQL Executor node but it generates this error

CREATE VIEW OPERACIONES AS
SELECT “sex”, AVG(“age”)
FROM “PRUEBAS”.“PUBLIC”.“CENSUS_TEST”
GROUP BY “sex”;

This error:

ERROR DB SQL Executor 4:220 Execute failed: Uncaught exception of type ‘STATEMENT_ERROR’ on line 20 at position 1 : Cannot perform CREATE VIEW. This session does not have a current database. Call ‘USE DATABASE’, or use a qualified name.

However, when using the statements separately it works perfectly, these are the statements that I used separately

BEGIN
USE DATABASE “PRUEBAS”;
CREATE VIEW “OPERACIONES”
AS SELECT “sex”
FROM “PRUEBAS”.“PUBLIC”.“CENSUS_TEST”;
END

SELECT “sex”, AVG(“age”) FROM “PRUEBAS”.“PUBLIC”.“CENSUS_TEST” GROUP BY “sex”

[mlauber71]

@slunah have you considered using something like

CREATE VIEW PRUEBAS.OPERACIONES

Also: what SQL database are you using?

[slunah]

Yes, I have already tried to use it in different ways and it still generates the error. If I use the way you gave me it gives me this error

I am using a connection to snowflake

[mlauber71]

@slunah you will have to try and use sufficient SQL code or use the KNIME nodes. Columns might need aliases or something.

[slunah]

this was the solution

BEGIN
USE DATABASE “PRUEBAS”;

CREATE OR REPLACE VIEW OPERACIONES2 AS
SELECT "sex", AVG("age") AS Promedio
FROM "PRUEBAS"."PUBLIC"."CENSUS_TEST"
GROUP BY "sex";

END

[takbb]

Hi @slunah, this may not be the same issue, but I can get KNIME to produce the same error if I have specified an incorrect name for the default database in the snowflake connector:

Do you have any other DB SQL Executor calls? It might help us to help you a little if we had a wider view of your workflow including the connector, and could see exactly the text you have in the DB SQL Executor. What happens if you include a USE DATABASE and/or USE SCHEMA command in a DB SQL Executor prior to the one containing the CREATE VIEW ?