How to loop over distinct values to create filtered views

[asj_bcn]

Hi to everyone,

I have a huge table and I want to create new views which are the result of filtering over the distinct values of one columns. After, I want to run a procedure to each of the new filtered tables.

First, I could create a new table with the distinct values and then run a loop to create the new views (as much as the number of distinct values). At every step, I could also run a procedure. I tried the following code (which does not run).

-- Drop existing tables
DROP VIEW "FILTER";
DROP VIEW "FILTERED_DATA";

-- Find unique values in a column/s
CREATE VIEW "FILTER" AS 
       SELECT DISTINCT "/BIC/S_WERKS","/BIC/S_MAT" 
             FROM "BW_SAP" 
             order by "/BIC/S_WERKS", "/BIC/S_MAT"
       ;   
CNTR := 0
-- Loop through the FILTER VIEW to Filter BW_SAP Table
WHILE CNTR < :NMBRROWS DO
CREATE VIEW "FILTERED_DATA" AS 
       SELECT  "/BIC/S_WERKS","/BIC/S_MAT" 
             FROM "BW_SAP" 
             order by "/BIC/S_WERKS", "/BIC/S_MAT"
       ;
CALL "ASJ"."AFM.PAL::LOVELY_CAT"();
END WHILE;

I managed to generate the distinct values tables but not to pass each row value to the loop.

Then, I tried to use cursors, but again I failed when running them. I attached two examples:

CURSOR "FILTERED"
       FOR
             SELECT DISTINCT "/BIC/S_WERKS","/BIC/S_MAT" 
                    FROM "ASJ"."BW_SAP"
                   order by "/BIC/S_WERKS", "/BIC/S_MAT"
             ;
       BEGIN
             FOR "FILTERED" AS FILTERED(:S_WERK,:S_MAT) DO
                    DROP VIEW "ASJ"."V_TS_DATA"
                    CREATE VIEW "ASJ"."V_TS_DATA" AS <br>                           SELECT "CAL_ID", SUM("/BIC/S_HIST") AS "/BIC/S_HIST" 
                                  FROM "ASJ"."BW_SAP"
                                  WHERE "/BIC/S_WERKS" = :S_WERK AND "/BIC/S_MAT" = :S_MAT
                                  GROUP BY "CAL_ID" 
                                  ORDER BY "CAL_ID"
                           ;
                    CALL "ASJ"."AFM.PAL::LOVELY_CAT"();
             END FOR;
       END;

CREATE PROCEDURE MY_CURSOR_PROC ()
LANGUAGE SQLSCRIPT
AS 
       CURSOR my_cursor
       FOR
             SELECT DISTINCT "/BIC/S_WERKS","/BIC/S_MAT" 
                    FROM "ASJ"."BW_SAP" 
                    order by "/BIC/S_WERKS", "/BIC/S_MAT"
             ;
BEGIN
   FOR mycursor_row AS my_cursor(:S_WERK,:S_MAT) DO
             DROP VIEW "ASJ"."V_TS_DATA"
             CREATE VIEW "ASJ"."V_TS_DATA" AS 
                    SELECT "CAL_ID", SUM("/BIC/S_HIST") AS "/BIC/S_HIST" 
                           FROM "ASJ"."BW_SAP"<br>                           WHERE "/BIC/S_WERKS" = :S_WERK AND "/BIC/S_MAT" = :S_MAT
                           GROUP BY "CAL_ID" 
                           ORDER BY "CAL_ID"
                    ;
             CALL "ASJ"."AFM.PAL::LOVELY_CAT"();
   END FOR;
END;

Any advise on what I am doing wrong or any light to a new approach will be very helpfull.

Thank you all.

AlexS

[lbreddemann]

Is about SQL Anywhere or HANA?

Also, what error did you receive?

Off hand, it looks as if you want to execute dynamic SQL (your CREATE VIEW statement) but you fail to use the EXEC command.

[asj_bcn]

Thank Lars for your comment. You are right: I am using SQL for HANA.

About the errors I get:

(A) Create distinct values table and then loop:

Statement 'CREATE VIEW "FILTER" AS SELECT DISTINCT "/BIC/S_WERKS","/BIC/S_MAT" FROM "BW_SAP" order by ...' 
successfully executed in 14 ms 585 µs  (server processing time: 13 ms 923 µs) - Rows Affected: 0 
Duration of 2 statements: 26 ms 

Could not execute 'CNTR := 0'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "CNTR": line 1 col 1 (at pos 1)
Could not execute 'WHILE CNTR < :NMBRROWS DO CREATE VIEW "FILTERED_DATA" AS SELECT "/BIC/S_WERKS","/BIC/S_MAT" FROM ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "WHILE": line 1 col 1 (at pos 1)

Statement 'CALL "ASJ"."AFM.PAL::LOVELY_CAT"()' 
successfully executed in 122 ms 535 µs  (server processing time: 112 ms 281 µs) - Rows Affected: 50 

Could not execute 'END WHILE'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)
<br>

(B) Cursor straightforward:

Could not execute 'CURSOR "FILTERED" FOR SELECT DISTINCT "/BIC/S_WERKS","/BIC/S_MAT" FROM "ASJ"."BW_SAP" order by ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "CURSOR": line 1 col 1 (at pos 1)
Could not execute 'BEGIN FOR "FILTERED" AS FILTERED(:S_WERK,:S_MAT) DO DROP VIEW "ASJ"."V_TS_DATA" CREATE VIEW ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "BEGIN": line 1 col 1 (at pos 1)

Statement 'CALL "ASJ"."AFM.PAL::LOVELY_CAT"()' 
successfully executed in 33 ms 871 µs  (server processing time: 12 ms 0 µs) - Rows Affected: 50 

Could not execute 'END FOR'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)
Could not execute 'END'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "END": line 1 col 1 (at pos 1)<br>

(C) Cursor with Procedure:

Could not execute 'CREATE PROCEDURE MY_CURSOR_PROC () LANGUAGE SQLSCRIPT AS CURSOR my_cursor FOR SELECT DISTINCT ...'
SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "CREATE": line 13 col 14 (at pos 395)

About the EXEC command, where I should use it?

[VolkerBarth]

Thank Lars for your comment. You are right: I am using SQL for HANA.

Then I would suggest to change the tags you have used to fit the topic.

-----

I'm not familiar with HANA - but generally spoken, is your goal not similar to grouping the huge table by that particular column and might simply be achieved by a

GROUP BY "/BIC/S_WERKS","/BIC/S_MAT"

- and then let the procedure run over each group, say, via a cursor?

(As stated, I do not know whether that would work with HANA.)

[asj_bcn]

Thank you for your suggestion Volker. I changed it to SAP HANA Studio to search for luck. 🙂

[lbreddemann]

Honestly, I still don't know what you are actually trying here.

Instead of creating and dropping views over and over again, why not just use parameters in your cursor?

Or you could create a single table function that takes your filter criteria as parameters and returns the aggregated result set.

I don't know what "LOVELY_CAT()" does, but it seems to expect data in a specific table or view. Can't you provide it with parameters to enable filtering?

[asj_bcn]

Dear Lars,

Thanks your for your answer. I want to run a statistical forecast for a lot of products using a HANA SQL procedure calling a AFL time series statistical function. The problem is the AFL functions only allow to run the function for one product at a time. A similar problem was reported by Nameeth Raman March 31, 2015 in his post Very log run time when using AFL-PAL functions to generate statistical forecast for 10,000 individual products (https://archive.sap.com/discussions/thread/3719685). I got stack in earlier steps when trying to apply the cursor to jump from one product forecast to the next. Nevertheless, I am not sure that using cursors is a good idea since it HANA is not very efficient at looping though data.

Thanks,

AlexS

[lbreddemann]

So, you're saying that in your system creating and dropping views is faster than using a cursor? Hard to believe.

Generally speaking, looping through data is inefficient in any SQL DBMS - it's the wrong approach to work with data.