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

[Former Member · Accepted solution]

Hi John,

Please pardon my ignorance as I did not go through the full code.

But

INSERT INTO "_SYS_BIC"."sts/SQLEPQ2" VALUES (:AD)

This won't work as SQLEPQ2 is just a View and you cannot insert data into a view.

--

Shreepad Patil

[Former Member · Accepted solution]

Thank you Shree, Jody, and Ravi,

The ignorance is all my end - I apologize and appreciate your help. I agree there are many issues. I have cleaned up the code and resolved many of the issues. I now can create the table, create the procedure, and call the procedure. Unfortunately I obtained 0 rows of data. Please see my TXT file attached.

I create a table with 3 primary keys/attributes and 2 measures from another calculation view(SQL). I also add 8 variable columns to this table.

I then create the procedure to build the table that contain the While Do statement.  The goal is to iterate on the 8 variable columns until the condition is met.

Finally I call the procedure.

PROCEDURE_TEXT.txt.zip

[Former Member · Accepted solution]

Hi John,

I have a few things in mind after going through the code.

1. I have a feeling that Table2 is view. Now if this is an Analytic View then the select query would not work as it requires group by clause.

2. I have not tried it but I think in the query when you say 'AS VAR_2', it creates an alias which is different from the variable VAR_2. So when you are referencing to :VAR_2. It will fetch the value of variable VAR_2, which is not initialized at all.

Recommend you to check the SELECT query progressively on the SQL Editor and/or within the procedure itself.

--

Shreepad

[Former Member · Accepted solution]

Thanks Shree

Your suggestions make total sense, Yes, Table 2 is an analytic view so I added an Group By and Order By.

I also see what you mean by VAR_2 so I eliminated VAR_2 through VAR_7 because they feed Var_8. This makes formulas longer but eliminate this issue as one formula variable feeds the next.

Since I am trying to do a While Do against Var_8, should I be adding a output variable against the procedure instead? I keep getting an error "SAP DBTech JBDC: [260] (at 112): invalid column name for Var_8.

The issue is I want to do a While Do against my last column (i.e. Var_8) in the table but how do I do a While Do such that this value stops when greater than zero,
.

This is why I am wondering if my procedure should specify an output instead? Or do I declare a variable equal to Var_8  I can't seem to get past an invalid column for While Do Var_8 > 0. .

PROCEDURE_TEXT v2.txt.zip

PROCEDURE_TEXT v3.txt.zip

[Former Member · Accepted solution]

Hi,

I have made some modifications to your code.

CREATE COLUMN TABLE "TABLE_2" (
    "ATT_PK1" NVARCHAR(18),
    "ATT_PK2" NVARCHAR(4),
    "ATT_PK3" NVARCHAR(4),
    PRIMARY KEY (ATT_PK1, ATT_PK2, ATT_PK3),
    "MEAS_1" DOUBLE,
    "MEAS_2" DECIMAL(5,2),
    "VAR_1" DOUBLE,
    "VAR_8" DOUBLE
    );

CREATE PROCEDURE BUILD_TABLE_2 ( ) LANGUAGE SQLSCRIPT AS

VAR_1 DOUBLE;
VAR_8 DOUBLE; --Change here

BEGIN

VAR_1 := 2;

WHILE  VAR_8 > 0 DO

INSERT INTO TABLE_2
SELECT
    "_SYS_BIC"."sts/TABLE_1"."ATT_PK1",
    "_SYS_BIC"."sts/TABLE_1"."ATT_PK2",
    "_SYS_BIC"."sts/TABLE_1"."ATT_PK3",
    "_SYS_BIC"."sts/TABLE_1"."MEAS_1",
    "_SYS_BIC"."sts/TABLE_1"."MEAS_2",

    :VAR_1,


((:VAR_1 + "_SYS_BIC"."sts/TABLE_1"."MEAS_2") - :VAR_1)  * ("_SYS_BIC"."sts/TABLE_1"."MEAS_1" )  AS "MEAS_3"   --Its just an alias(Will display the table with this column name)

FROM "_SYS_BIC"."sts/TABLE_1"


GROUP BY    
ATT_PK1, ATT_PK2, ATT_PK3,
"_SYS_BIC"."sts/TABLE_1"."MEAS_1",
"_SYS_BIC"."sts/TABLE_1"."MEAS_2"

ORDER BY
ATT_PK1, ATT_PK2, ATT_PK3;


--Add this query
SELECT TOP 1 ((:VAR_1 + "_SYS_BIC"."sts/TABLE_1"."MEAS_2") - :VAR_1)  * ("_SYS_BIC"."sts/TABLE_1"."MEAS_1" )  INTO VAR_8 FROM "_SYS_BIC"."sts/TABLE_1"

GROUP BY    
ATT_PK1, ATT_PK2, ATT_PK3,
"_SYS_BIC"."sts/TABLE_1"."MEAS_1",
"_SYS_BIC"."sts/TABLE_1"."MEAS_2"
ORDER BY
ATT_PK1, ATT_PK2, ATT_PK3 ASC ; --or DESC as per requirement
--end of query


VAR_1 := VAR_1 + 1;


END WHILE;

END;

CALL BUILD_TABLE_2 ( )

Very Important: You will have to use the ASC or DESC very carefully.

Hope you get what I am trying to do and build your code accordingly.

--

Shreepad

[Former Member · Accepted solution]

Hi Shree

Thank you, your suggestion makes sense and have been trying many ways. I can build the procedure but I still struggle with zero rows when I call the procedure. I am now getting the following error when I call the procedure

"Could not execute 'CALL BUILD_TABLE_2 ()' in 233 ms 812 us, SAP DBTech JDBC: [340]: not all variable bound: [340] SYSTEM.BUILD_TABLE_2: line 13 col 3 (at pos 161): not all variables bound exception:  not all variables bound:  [340] not all variables bound: unbound parameter : 0 of 1 Please check lines"

I am attaching my code as txt files - it was also suggested to utilize a Loop Over Curser and I have attached a text version of this as well. At the end of the code I have added the error I get this method as well.

Hopefully you can help point me in the right direction

Thanks

John

Loop Over Cursor.txt.zip

WHILE DO.txt.zip

[Former Member · Accepted solution]

Hi John,

For the code without cursor.

1.

I can build the procedure but I still struggle with zero rows when I call the procedure.

Sometimes the SQL Console will not show the results properly while running a procedure, can you check the table directly instead.

SELECT * FROM TABLE_2;

2. If the earlier step also shows 0 records in the table then can you try running the SQL Statement separately in the console

SELECT
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK1", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK2", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK3",
        "_SYS_BIC"."sts/TABLE_1"."MEAS_1", 
        2,
        "_SYS_BIC"."sts/TABLE_1"."MEAS_1" - 2

        FROM "_SYS_BIC"."sts/TABLE_1"

        GROUP BY
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK1", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK2", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK3",
        "_SYS_BIC"."sts/TABLE_1"."MEAS_1"

        ORDER BY
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK1", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK2", 
        "_SYS_BIC"."sts/TABLE_1"."ATT_PK3";

Notice that I have removed the :VAR_1 and replaced it with 2, That is the value of the variable in first iteration.

As far as the code with Cursor.

1. Declare statements should be the 1st statementa after BEGIN, Can you remove the VAR_1 assignment from that position and make it go after the declare.

2. When you loop over the cursor, use parenthesis after the cursor name

    FOR cur_row as CURSOR1() DO

Though as you mention, using a Cursor is a bad idea(Its a real performance killer). If this is a critical code then I would advise against the use of cursors.

Let us know if it works.

--

Shreepad

[Former Member · Accepted solution]

Hi Shree

I think we are getting closer - thank you. I ran step 1 as you suggested and the result was 0 rows. I then ran step 2 and I did get all my rows.

Does this mean it is not being inserted into the table or something is wrong with the while do? I added the Insert Into statement for Step 2 and then Selected Table_2 - this did bring up the rows of data, so it seems to be the while do.

Thanks again - you have been very helpful

John

[Former Member · Accepted solution]

Hi John,

The tests show results which are quite surprising. Although the debugging you did show that the problem might be with the WHILE..DO loop, but it is looking very fine to me..

Before we conclude that the problem is with the loop, one more thing you can try is put this statement inside the loop as the first line after DO

SELECT :VAR_2 FROM DUMMY;

and then call the procedure.

If the loop is getting executed then it should open up result tabs with the value of VAR_2 for each iteration. This would confirm whether the loop is getting executed or not.

--

Shreepad

[Former Member · Accepted solution]

Hi Shree

Thanks.  I tried as you suggested and still got zero rows when I called the procedure. I also tried Select * From Table_2 and only showed the Header Row.

I tried the procedure two ways (see attached text files), one where I do not set VAR_2 equal to anything and another where I set it equal to 1. The first way (Method 1), I get zero rows. The second way (Method 2), I get the following error:

Could not execute 'CALL BUILD_TABLE_2 ( )' in 161 ms 191 us, SAP DBTech JBDC: [340]: not all variable bound: [340] SYSTEM.BUILD_TABLE_2: line 16 col 3 (at pos 194): not all variables bound exception: not all variables bound: [340] not all variables bound: unbound parameter : 0 of 1Please check lines:

Jan 13 2014 method 2.txt.zip

Jan 13 2014 method 1.txt.zip

[Former Member · Accepted solution]

Hi John,

Method 2 would give an error for sure as loop's WHILE condition would check whether VAR_2 > 0 and whereas the VAR_2 variable does not hold any value.

However, you should get a similar result(as given below) when trying to execute via the method 1

I tried to build a similar procedure(just the loop part of it)

Output:

If the code goes through loop one should get similar kind of display. If you're getting these kind of screens in method 1 then the select statement could be faulty otherwise if you are not getting these screens then the loop itself has an issue.

I am not able to see why it is not working. The code seems to be correct.

Can you try the same code I have written in your system. Let's see if this piece of code works.

Which version of HANA DB are you using? There might be a slight possibility of bugs with the version you're using. I am on HANA Rev 70.

--

Shreepad

[Former Member · Accepted solution]

Hi Shree

Thanks. I ran the same code and got the same results as you.

I am using Rev 68 and will update to 70 tonight.

I noticed you had "WHILE :VAR_1 > 0 DO" instead "WHILE VAR_1 > 0 DO". I made this change in my original code and this did not make a difference - still no rows of data. I was hoping it would be as simple as this ; )

Could it be that in your code we only use VAR_1. First in the While Do and then second we iterate on it by subtracting one.?

This is in comparison having VAR_2 in the While Do and iterate using VAR_1. Plus I am using data from the table to calculate VAR_2.

Do I need to be doing more with VAR_2?

Thanks again!

[Former Member · Accepted solution]

Hi John,

Yes, I agree that you are doing much more with the looping variable VAR_2, but it should have gone inside the loop for the first time at least.

If it went inside the loop then it should have been able to print the VAR_2 at least once.

Let us know if the upgrade made this piece of code work. Until the upgrade can you try declaring the variable as INTEGER and running the loop..not sure whether it would work or not.

Again can you try writing a similar display statement such as

SELECT 5 FROM DUMMY;

put this statement outside the loop and check whether it gives an output while running the procedure.

--

Shreepad

[Former Member · Accepted solution]

Shree - I want to thank you for all your help -  I finally got my While Loop working correctly.

[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

[Former Member]

Hi John,

As implied by Shreepad, please provide a simplified version of your code. I suspect there are more issues than the (useful) one Shree mentioned.

Then folks can help address the errors, and you can then re-factor all of your code.

Cheers,

Jody

[Former Member]

Thanks Jody - please see my response to Shree