Hi,
We are trying to insert into a table using a CTE (WITH clause query).
In the insert we are using the INDENTIFIER function as the catalog name is retrieved dynamically.
This is causing the insert to fail with an error - The table or view `cte_query` cannot be found.
Any idea how this cam be fixed?
Example
CREATE TABLE catalog_name.schema_name.target_table1
(
col1 string,
col2 date
);
DECLARE VARIABLE var_catalog_name STRING;
SET var_catalog_name = (select catalog_name from system.information_schema.catalogs where catalog_name like 'my_catalog%');
INSERT INTO IDENTIFIER(var_catalog_name || '.schema_name.target_table1')
(
col1,
col2
)
WITH cte_query as
(
select 'A' as col1,
current_date() as col2
)
select col1,
col2
from cte_query
;
