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 http://<hostname>:51013/workshop/admin/ui/exerciseMaster/?workshop=dev602&sub=ex2_26
PROCEDURE "dev602.procedures::get_product_by_filter" (
IN im_product_filter_string varchar(5000),
IN im_start_date DATE,
IN im_end_date DATE,
OUT EX_PRODUCTS TABLE (
PRODUCTID NVARCHAR(10),
DELIVERYDATE DAYDATE,
NUM_DELIVERED_PRODUCTS BIGINT,
CUMULATIVE_SUM BIGINT ) )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
--DEFAULT SCHEMA <default_schema_name>
AS
BEGIN
DECLARE temp_date DATE;
DECLARE local_start_date DATE = :im_start_date;
DECLARE local_end_date DATE = :im_end_date;
DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001;
BEGIN
DECLARE EXIT HANDLER FOR MYCOND
BEGIN
DECLARE parameter NVARCHAR(256) = 'start_date = '||
:local_start_date ||
' end_date = '||
:local_end_date;
temp_date = :local_start_date;
local_start_date = :local_end_date;
local_end_date = :temp_date;
INSERT INTO "dev602.data::log.errors"
VALUES
(current_timestamp, :parameter , ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE);
END;
if :im_start_date > :im_end_date THEN
SIGNAL MYCOND SET MESSAGE_TEXT = 'Start date must be smaller then end date';
END IF;
END;
pre_filtered_products =
SELECT * FROM "dev602.data::MD.Products" WHERE CATEGORY NOT IN ('Laser Printer');
user_filtered_products = APPLY_FILTER(:pre_filtered_products, :im_product_filter_string ) ;
filtered_items =
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"
where pi.DELIVERYDATE >= :local_start_date
AND pi.DELIVERYDATE <= :local_end_date;
aggregated_filtered_items =
SELECT PRODUCTID, DELIVERYDATE,
COUNT(PRODUCTID) AS NUM_DELIVERED_PRODUCTS FROM :filtered_items
GROUP BY PRODUCTID ,DELIVERYDATE
ORDER BY PRODUCTID, DELIVERYDATE;
CALL "dev602.procedures::calculate_cumulative_sum_of_delivered_products"(
IM_PRODUCTS => :aggregated_filtered_items,
EX_PRODUCTS => :products );
ex_products = select * from :PRODUCTS order by PRODUCTID, DELIVERYDATE;
END;