Skip to Content
avatar image
Former Member

How to loop over distinct values to create filtered views

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

Add comment
10|10000 characters needed characters exceeded

  • Former Member Lars Breddemann

    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?

  • 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.)

  • Former Member Volker Barth

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

  • Get RSS Feed

1 Answer

  • Mar 24, 2017 at 02:56 AM

    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?

    Add comment
    10|10000 characters needed characters exceeded

    • 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.