Skip to Content
Previous

Using Index Based Cell access

By Craig Cmehil

Leveraging SQLScript in Stored Procedures & User Defined Functions

You will learn

This solution shows how to use index-based cell access to achieve the same. This option is the fastest among the solutions.
Please note - This tutorial is based on SPS11

Details


Step 1: Edit previous procedure

Return to the procedure called calculate_cumulative_sum_of_delivered_products.

procedure editor

Delete all of the logic in the body of the procedure except for the declare statement for the i variable.

delete logic
Please log in to access this content.
Step 2: Insert FOR loop

Next, instead of using arrays to access the input parameter table values and calculate the values, we will use index-based cell access to access the cells of both the input and output parameter directly. Insert the FOR loop as shown.

for loop
Please log in to access this content.
Step 3: Check complete code

The completed code should look like the following. If you do not wish to type this code, you can reference the solution web page at http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_23

PROCEDURE "dev602.procedures::calculate_cumulative_sum_of_delivered_products" (
  IN IM_PRODUCTS TABLE ( PRODUCTID NVARCHAR(10),  
                         DELIVERYDATE DAYDATE,
                        NUM_DELIVERED_PRODUCTS BIGINT ),
  OUT EX_PRODUCTS TABLE ( PRODUCTID NVARCHAR(10),
                          DELIVERYDATE DAYDATE,
                          NUM_DELIVERED_PRODUCTS BIGINT,
                        CUMULATIVE_SUM BIGINT )  )
 LANGUAGE SQLSCRIPT
 SQL SECURITY INVOKER
 READS SQL DATA AS
BEGIN

  DECLARE i  INTEGER  = 1;

  FOR i IN 1..CARDINALITY(ARRAY_AGG(:IM_PRODUCTS.PRODUCTID)) DO

     EX_PRODUCTS.PRODUCTID[:i]   = :IM_PRODUCTS.PRODUCTID[:i];
     EX_PRODUCTS.DELIVERYDATE[:i]  =  :IM_PRODUCTS.DELIVERYDATE[:i] ;
     EX_PRODUCTS.NUM_DELIVERED_PRODUCTS[:i] = :IM_PRODUCTS.NUM_DELIVERED_PRODUCTS[:i];     

     if :i = 1 then
       EX_PRODUCTS.CUMULATIVE_SUM[:i] = :IM_PRODUCTS.NUM_DELIVERED_PRODUCTS[:i];
       continue;
     end if;

     IF :IM_PRODUCTS.PRODUCTID[:i-1] <> :IM_PRODUCTS.PRODUCTID[:i]  THEN
       EX_PRODUCTS.CUMULATIVE_SUM[:i] = :IM_PRODUCTS.NUM_DELIVERED_PRODUCTS[:i];
     ELSE
       EX_PRODUCTS.CUMULATIVE_SUM[:i] = :EX_PRODUCTS.CUMULATIVE_SUM[:i-1]
                  + :IM_PRODUCTS.NUM_DELIVERED_PRODUCTS[:i];
     END IF;

  END FOR;

END
Please log in to access this content.
Step 4: Save and build

Click Save.

save

Use what you have learned already and perform a build on your hdb module.

Please log in to access this content.
Step 5: Run call statement again

Return to the HRTT page and run the call statement again.

HRTT
Please log in to access this content.
Step 6: Check the results

Check the results.

results

Notice the execution time is a little bit less than when doing the calculation using SQL, or using cursors or arrays.

execution time
Please log in to access this content.

Next Steps

Updated 03/23/2017

Time to Complete

10 Min.

Intermediate

Prerequisites

Next
Back to top