replace PROCEDURE mydb.sp_Insert_Values ( IN lastExecDate timestamp ) SQL SECURITY CREATOR BEGIN CREATE MULTISET VOLATILE TABLE vt_ref_table_1 ( Ref_Id integer, Ref_Unit_Type varchar(50) ) ON COMMIT PRESERVE ROWS; insert into mydb.vt_ref_table_1 select Ref_Id, Ref_Unit_Type from mydb.ref_table_1; INSERT INTO mydb.Time_Series_Table select t1.TD_TIMECODE , t1.Time_Series_Meas , t1.Time_Series_Curve_Type_CD , t1.Ref_Unit_Type , t1.Ref_Id , t1.created_on from ( select meas_ts as TD_TIMECODE , ref_table_1.Ref_Id as Ref_Id , meas as Time_Series_Meas, , Time_Series_Curve_Type_CD , Ref_Unit_Type , current_timestamp as created_on from mydb_stg.Time_Series_Table_Stg as stg left join mydb.ref_table_1 as ref_table_1 on ref_table_1.Ref_Id = stg.Ref_Id where stg.created_on >= :lastExecDate ) as t1 left join mydb.Time_Series_Table as t2 on t1.TD_TIMECODE=t2.TD_TIMECODE and t1.Time_Series_Curve_Type_CD = t2.Time_Series_Curve_Type_CD and t1.Ref_Id=t2.Ref_Id where t2.Ref_Id is null ; END;
It compiles but when I call it this error is thrown:
Only a COMMIT WORK or null statement is legal after a DDL Statement.
I know the error is related to the volatile table but I don't know how to correct it.
Why I need the volatile table:
The reference table has a row-level-security constraint. If I use it directly I get another error:
A multi-table operation is executed and the tables do not have the same security constraints.
I simplified and anonymized the query. I know I still need to include some error handling.
Teradata Version: 16.20
Any comment will be appreciated.