Skip to main content
Question

Using variables in Query

  • July 7, 2022
  • 1 reply
  • 3 views
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

    1 reply

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • July 7, 2022

    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

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement