Join us on May 14-15, 2019 at Forrester Digital Transformation & Innovation 2019 in Chicago, USA.

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagesql
+--------+------------+---------+---------------------------------+

| 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
languagesql
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.

Image Added

This is the outcome.

Image Added