I get a syntax error in the line marked bold below that I dont know how to solve. I am trying to UPDATE parameter p2 which is a table.
The error states "Dependent object not found: SqlScript; SAPHANAABAP.P2: symbol not found" where SAPHANAABAP is the default schema.
I tried ':p2' but it does not work either 😔
Also, if I have coded something which is against best practice SQL pls dont be shy to point it out. This is my very first SQL procedure so I am new at this.
PROCEDURE "DEMO (IN p1 inTab, OUT p2 outTab)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
/* READS SQL DATA*/
AS
BEGIN
/*standard variables*/
DECLARE v_curr_scuni0 VARCHAR(30);
DECLARE v_num_scuni0 INTEGER;
DECLARE v_seasi0 INTEGER;
DECLARE v_month INTEGER;
DECLARE v_number INTEGER;
/*array declarations*/
DECLARE a_scuni0 NVARCHAR(30) ARRAY;
DECLARE v_array_index INTEGER := 1;
/*Parameter table*/
drop table SAPHANAABAP.PARAMETER;
CREATE TABLE SAPHANAABAP.PARAMETERS(NAME VARCHAR(60), INTARGS INTEGER);
INSERT INTO SAPHANAABAP.PARAMETERS ('OTTO', null);
p4 = SELECT * FROM SAPHANAABAP.PARAMETERS;
p2 = SELECT * FROM :p1;
/*get all units*/
t_scuni0 = SELECT SCUNI0 FROM :p1 GROUP BY SCUNI0;
/*build array of units*/
a_scuni0 := ARRAY_AGG(:t_scuni0.SCUNI0);
/*get number of units*/
SELECT count(*) INTO v_num_scuni0 FROM :t_scuni0;
WHILE :v_array_index <= :v_num_scuni0 DO
v_curr_scuni0 := :a_scuni0[:v_array_index];
p3 = SELECT month AS ID, seaaa0 AS VALUE FROM :p1
WHERE SCUNI0 = :v_curr_scuni0;
call procedure_temp (:p3, :p4, :p5);
v_month := 1;
WHILE v_month <= 12 DO
SELECT ID INTO v_number FROM :p5 WHERE ID = v_month;
UPDATE p2 SET SEASI0 = v_number WHERE SCUNI0 = :v_curr_scuni0 AND month = :v_month;
END WHILE;
v_array_index := :v_array_index + 1;
END WHILE;
END;