ALTER SESSION SET CURRENT_SCHEMA = SqlScriptDocumentation;

DROP PROCEDURE addDiscount; 
CREATE PROCEDURE addDiscount( IN it_books tt_sales_books, OUT ot_books tt_sales_books) 
    LANGUAGE SQLSCRIPT READS SQL DATA AS 
BEGIN 
  ot_Books = SELECT title, CASE WHEN price > 300 THEN (price - (price / 30)) 
                                ELSE CASE WHEN price > 200 THEN (price - (price / 20)) 
                                          ELSE (price - (price / 10)) 
                                     END 
                           END AS price, crcy 
             FROM :it_books; 
END;

DROP PROCEDURE getSalesBooks; 
DROP VIEW addDiscount_RET; 
CREATE PROCEDURE getSalesBooks( IN minPrice DECIMAL(5, 2), IN currency VARCHAR(3), 
                                IN it_books books, OUT ot_sales tt_sales_books) 
   LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW addDiscount_RET AS 
BEGIN 
  lt_expensive_books = SELECT title, price, crcy 
                       FROM :it_books 
                       WHERE price > :minPrice 
                       AND crcy = :currency;

  CALL addDiscount(:lt_expensive_books, lt_on_sale);

  lt_cheap_books = SELECT title, price, crcy 
                   FROM :it_books 
                   WHERE price <= :minPrice 
                   AND crcy = :currency;

  ot_sales = CE_UNION_ALL(:lt_on_sale, :lt_cheap_books); 
END;

CALL getSalesBooks(1.5, '''EUR''', books, op_sales_books); 
TRUNCATE table op_sales_books; 
SELECT * FROM addDiscount_RET WITH PARAMETERS ( 'placeholder' = ('$$minprice$$', '1'), 
                                     'placeholder' = ('$$currency$$', '''EUR'''), 
                                     'placeholder' = ('$$it_books$$', 'books'), 
                                     'placeholder' = ('$$ot_sales$$', 'op_sales_books')); 
TRUNCATE table op_sales_books;