on 11-17-2014 8:47 PM
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;
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.