Using variables in Query

yehudit

Hi,
get an error when I run the following query:

DO $$
DECLARE
Is_Full_Exect INT;
BEGIN
Is_Full_Exect:= 5;
SELECT stg.new_ncoronavinvestigation,stg.new_labresultid,'stg_Filterednew_LabResult_Is_Full_Exect' AS Update_Source
FROM stg.Filterednew_LabResult stg
WHERE new_labresultid=:Is_Full_Exect;
END;
$$;

I would appreciate your help to recognize my syntex error
TNX

Bryan_H

It's necessary to use EXECUTE to perform variable substitution where the SQL query is a string as follows:
EXECUTE 'SELECT stg.new_ncoronavinvestigation,stg.new_labresultid,stg_Filterednew_LabResult_Is_Full_Exect AS Update_Source FROM stg.Filterednew_LabResult stg WHERE new_labresultid='||Is_Full_Exect;
You can also use $1,$2,... for position-based substitution. Please see documentation at https://www.vertica.com/docs/11.1.x/HTML/Content/Authoring/ExtendingVertica/StoredProcedures/PLvSQL/EmbeddedSQL.htm