cancel
Showing results for 
Search instead for 
Did you mean: 

How to UPDATE parameter table?

Former Member
0 Kudos

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;

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

Looks who's back

The reason for why you cannot update, insert or delete table p2 is: there is no table p2.

In your code p2 is a table variable and table variables cannot be used with UPDATE, DELETE, INSERT or UPSERT.

Not sure about the rest of the code (grouping a single column without aggregation function? any objection to the more expressive SELECT DISTINCT?) either.

And the last WHILE loop does not terminate...

Anyhow - your update - problem is simply that you cannot update table variables. It's documented, it has been discussed, not big news.