Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
Using variables in Query
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
0
Leave a Comment
Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.
Answers
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