get_product_by_filter procedure in the editor. Delete the output parameter.
Define a new output parameter called
EX_PRODUCTS. Instead of referring to predefined table of table type we are now using in place table type definition
Enter another SELECT statement which does an INNER JOIN between the results of the previous SELECT statement and the
PO.Item table as shown here. Assign this statement to a table variable named
Next enter another SELECT which aggregates the results of the previous SELECT statement. Assign this statement to a table variable named
Finally, add another SELECT statement which does a self inner join to calculate the cumulative sum. Assign this statement to the output table parameter
The completed code should look very similar to the following. If you do not wish to type this code, you can reference the solution web page at ```
PROCEDURE “dev602.procedures::get_product_by_filter” (
IN im_product_filter_string varchar(5000),
OUT EX_PRODUCTS TABLE (
CUMULATIVE_SUM BIGINT ) )
SQL SECURITY INVOKER
READS SQL DATA AS
SELECT * FROM “dev602.data::MD.Products” WHERE CATEGORY NOT IN (‘Laser Printer’);
user_filtered_products = APPLY_FILTER(:pre_filtered_products, :im_product_filter_string ) ;
SELECT pi.“PRODUCT.PRODUCTID” as PRODUCTID, pi.DELIVERYDATE FROM :user_filtered_products as p
INNER JOIN “dev602.data::PO.Item” as pi on p.productid = pi.“PRODUCT.PRODUCTID” ;
SELECT PRODUCTID, DELIVERYDATE,
COUNT(PRODUCTID) AS NUM_DELIVERED_PRODUCTS FROM :filtered_items
GROUP BY PRODUCTID ,DELIVERYDATE
ORDER BY PRODUCTID, DELIVERYDATE;
SUM(p2.NUM_DELIVERED_PRODUCTS) AS CUMULATIVE_SUM
FROM :aggregated_filtered_items as p1
INNER JOIN :aggregated_filtered_items as p2
ON p1.PRODUCTID = p2.PRODUCTID
and p1.DELIVERYDATE >= p2.DELIVERYDATE
GROUP BY p1.PRODUCTID,p1.DELIVERYDATE,
ORDER BY PRODUCTID, DELIVERYDATE ;
Use what you have learned already and perform a build on your
hdb module. Then return to the HRTT page and invoke the procedure again.
Click “Format Code”.
Enter the value for the input parameter as shown. Then run the call statement.
'CATEGORY = ''Notebooks'' OR CATEGORY = ''PC'''
View the results.
Take note of the run time.