Skip to Content
0
Former Member
Nov 17, 2014 at 08:47 PM

How to UPDATE parameter table?

826 Views

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;