How to do a WHILE DO loop on data from a SQL Calculation View

[Former Member]

I would like to do a WHILE DO loop, I am pulling columns from a SQL Calculation View to Do the iteration. I want to start with a value AD = 2 and iterate until a condition is met. In addition to the columns I pull in, I also need to calculate 6 new columns (AT1, AU, AE, X, AV, and V). These columns could be combined into one but I have been keeping them separate to keep the final formula for V shorter.  These values will change as a function of AD until the condition for V is met. I can pull in the columns from my SQL Calculation View, I just am not able to iterate.
My code looks something this (I have simplified the formulas some to make it shorter) but it gives you an idea what I am trying to do.
DECLARE AD DOUBLE := 2;
WHILE :V > 0 DO
SELECT MATNR, WERKS, YEAR1, AVG_QUANTITY, STDDEV_QUANTITY,
AVG_ACTUAL, STDDEV_ACTUAL, STDDEV_YIELD, ZSTCT, LOSFX,
ZSTCC, LGRAD, VERPR, LKOST, ITERATION, DEMAND_TERM, DEMAND_TERM_SQD,
SUPPLY_TERM, SUPPLY_TERM_SQD, YIELD_TERM, YIELD_TERM_SQD,
SUM_OF_SQUARES, STDDEV_DEMAND_LT,
AT1 := ((:AD)/(STDDEV_DEMAND_LT)),
AV := (SQRT(20/POWER( :AT1 ,2))),
AU := ((5.6211054 * :AV) + (-3.883683 * (POWER(:AV,2)))),
AE := (STDDEV_DEMAND_LT)* :AU,
X := :AE/(52*(AVG_QUANTITY)),
V := ((:AD+ITERATION)-:AD)*((1-ZSTCT)+(ZSTCC* :X))
FROM "_SYS_BIC"."sts/SQLEPQ1";
IF :V < 0 THEN
INSERT INTO "_SYS_BIC"."sts/SQLEPQ2" VALUES (:AD);
END IF;
AD := :AD+ITERATION;
END WHILE;
I keep getting an error "Internal deployment of object failed;Repository: Encountered an error in repository runtime extension;Internal Error:Deploy Calculation View: SQL: sql syntax error: incorrect syntax near "DECLARE": line 8 col 1" . I have tried many ways and get errors around the DECLARE or WHILE.
If there is a better way - I am open

Thanks

[Ravi_Channe]

Hi John,

Couple of points from my side.

You may wish to use SELECT ... INTO ... statement to populate the declared variables. I am not sure if the direct value assignment to the variable in the SELECT statement is supported.

Secondly as mentioned above, the HANA information models (HANA Views) are column views as read only objects. These are not data bearing objects, hence you cannot insert data to the views directly. Instead you may want to insert data to the underlying physical tables. Once the data is inserted to the underlying physical tables, it will be reflected in the view.

Regards,

Ravi

[Former Member]

Thanks Ravi - please see my response to Shree