Main menu

Transaction Open Batches Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:51

This was a fun procedure to write. I originally found the IFNULL function for my Account Balance Store Procedure. This query, when directly implemented in PHP was MUCH more complicated because of the possibilities of returning a NULL value. Now, it's all done in one statement.

<?php
--
-- Transaction Open Batches Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_OpenBatches;

DELIMITER |
CREATE PROCEDURE Transaction_OpenBatches (IN batch_account_id INT)
BEGIN
  SELECT
        <code>transaction</code>.<code>batch_number</code> AS batch_no,
      IFNULL(
            IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>batch_number</code> = <code>batch_no</code> AND <code>credit_account_id</code> = batch_account_id
           ),0)
       -
          IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>batch_number</code> = <code>batch_no</code> AND <code>debit_account_id</code> = batch_account_id
            ),0)
       ,0) AS batch_balance
   FROM
       <code>transaction</code>
  WHERE
     <code>transaction</code>.<code>batch_number</code> > 0
    GROUP BY
       <code>transaction</code>.<code>batch_number</code>
   HAVING
     batch_balance > 0;
END
|

DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
cics
mysqli
stored procedure