...
| Code Block | ||
|---|---|---|
| ||
+--------+------------+---------+---------------------------------+ | c_name | c_quantity | c_price | c_parent_id | +--------+------------+---------+---------------------------------+ | pen | 1 | 10 | 1177_purchaseRequition_purchase | | pencil | 2 | 20 | 1177_purchaseRequition_purchase | | SUM | 3 | 30 | 1177_purchaseRequition_purchase | +--------+------------+---------+---------------------------------+ |
However, it does not actually play out well with the JDBC Binder as it is only expecting one parameter in the query. Our union query has 2 parameters.
In order to overcome this, we can create a stored procedure in the database instead.
| Code Block | ||
|---|---|---|
| ||
DELIMITER //
CREATE PROCEDURE purchase_items_dataset
(IN recordId CHAR(255))
BEGIN
select c_name, c_quantity, c_price, c_parent_id from app_fd_purchase_items where c_parent_id = recordId
union
select "SUM", sum(c_quantity), sum(c_price), c_parent_id from app_fd_purchase_items where c_parent_id = recordId;
END //
DELIMITER ; |
With the stored procedure to return the appropriate dataset that we need, we will just need to call it from the JDBC Binder.
This is the outcome.
