Hi! How to solve this error

AnelyaAnelya Community Edition User
edited May 2023 in General Discussion

Error:
SQL Error [4856] [42601]: [Vertica]VJDBC ERROR: Syntax error at or near "00" at character 0

I have to write a procedure that returns me table, where will be table_name, column_name(LIKE %LOAD_DATE%) AND MAX DATE of colomn_name(LIKE %LOAD_DATE%)

1) I create temp table:

CREATE TEMPORARY TABLE max_load_dates (
table_name varchar(64),
load_date_col varchar(64),
max_date varchar(64));

2)There are my procedure:

CREATE OR REPLACE PROCEDURE max_load_d()
AS $$
DECLARE
table_name_1 varchar(64);
load_date_col varchar(64);
max_date varchar(64);
BEGIN
FOR table_name_1 IN QUERY (SELECT table_name FROM v_catalog.columns WHERE table_schema = 'public' and column_name LIKE '%LOAD_DATE%')
LOOP
SELECT column_name INTO load_date_col FROM v_catalog.columns
WHERE table_name = table_name_1 AND column_name LIKE '%LOAD_DATE%';
IF load_date_col IS NULL THEN
RAISE EXCEPTION 'В таблице % нет колонки, содержащей строку LOAD_DATE', table_name_1;
END IF;
BEGIN
EXECUTE 'SELECT MAX(' || load_date_col || ') FROM public.' || table_name_1 INTO max_date;
IF max_date IS NULL THEN
max_date := NULL;
END IF;
EXCEPTION
WHEN no_data_found THEN
max_date := NULL;
END;
EXECUTE 'INSERT INTO max_load_dates VALUES (''' ||table_name_1|| ''', ''' || load_date_col || ''', ' || coalesce(max_date::varchar, 'null') || ')';
END LOOP;
END;
$$ LANGUAGE PLpgSQL;

CALL max_load_d();

Best Answer

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file