If you've got a 4GL , 4Js, or Genero development licence then you probably seldom use Informix Ace reports, but they can be a very useful tool not just for reporting but for database housekeeping exercises.

The problem

So, you've got the Inhouse housing management system and (somehow!) your tenant balances have got out of step and you need an easy, and transparent, way of fixing these balances. One of the key things is that you want to check the records that will be fixed before applying any table updates.

Logic

Essentially the balance on the tenant2 table is the SUM of the associated transactions on the items table, so it is simply a case of applying the following logic:

    FOREACH tenant account
       SUM all transactions
       IF calculated SUM != tenant account balance THEN
          UPDATE tenant account balance
       END IF
    END FOREACH

Now its never quite that simple (e.g. missing transaction records etc) hence the need to CHECK the resultant SQL before applying them!

The tools for the job..

Now there are lots of ways of doing this, you could write a program to generate an SQL file, or you could do it via (rather complex) Informix query, but why not use Ace reports.

The code below is an extract from an ACE report that will do just that.

{=============================================================================
ACE Report: ten2_upd.ace
Description:
~~~~~~~~~~~~
ACE Report to create an SQL file to update the tenant account balances & items
(based on SUMMING).

TABLES Used:  items              - Inhouse transactions
              tenant2            - Inhouse accounts

OUTPUT File:  ten2_upd.sql
=============================================================================}

{----Database----}
DATABASE lgh
END

{----Variable Defn----}
DEFINE
PARAM [1] p_pref CHAR(16)
VARIABLE  v_ct   SMALLINT
VARIABLE  v_bal  DECIMAL(10,2)
END

{----Output Defn----}
OUTPUT 
REPORT TO     "ten2_upd.sql"
TOP    MARGIN 0
BOTTOM MARGIN 0
LEFT   MARGIN 0
RIGHT  MARGIN 0
END

{----Selection----}
SELECT t2.pro_ref,
       t2.seq_no,
       t2.current_balance,
       i.tx_ref,
       i.item_amount,
       i.balance
FROM   tenant2      t2,
       OUTER items  i
WHERE  t2.pro_ref = i.pro_ref
AND    t2.seq_no  = i.seq_no 
AND    t2.pro_ref MATCHES $p_pref
ORDER BY
       pro_ref,
       seq_no,
       tx_ref
END

{----Report Defn----}
FORMAT
FIRST PAGE HEADER
   LET v_ct  = 0
   { Print file header }
   PRINT '{================================================================='
   PRINT ' SQL File: ten2_upd.sql'
   PRINT ' Description:'
   PRINT ' SQL to update the tenant account balances as applic.'
   PRINT '=================================================================}'
   SKIP 1 LINE
   PRINT 'DATABASE lgh;'
   SKIP 1 LINE
   PRINT "BEGIN WORK;"

BEFORE GROUP OF seq_no
   LET v_bal = 0

ON EVERY ROW
   IF item_amount IS NOT NULL THEN
      LET v_bal = v_bal + item_amount
   {}
   IF v_bal != balance THEN
   BEGIN
      SKIP 1 LINE
      PRINT "{*** TX Ref Mismatch!!! ***}"
      PRINT "UPDATE items"
      PRINT "SET    balance = ", v_bal USING "-<<<<<<&.&&"
      PRINT "WHERE  tx_ref  = ", tx_ref USING "<<<<<<<&"
      PRINT "AND    pro_ref = '", pro_ref CLIPPED, "'"
      PRINT "AND    seq_no  = '", seq_no  CLIPPED, "';"
   END
 
AFTER GROUP OF seq_no
   IF v_bal != current_balance THEN 
   BEGIN
      LET v_ct = v_ct + 1
      { Print it }
      SKIP 1 LINE
      PRINT "{ ", v_ct USING "<<<<&", 
            ". Account ", pro_ref CLIPPED, "/", seq_no CLIPPED, " }" 
      PRINT "UPDATE tenant2"
      PRINT "SET    current_balance = ", v_bal USING "-<<<<<<&.&&"
      PRINT "WHERE  pro_ref         = '", pro_ref CLIPPED, "'"
      PRINT "AND    seq_no          = '", seq_no  CLIPPED, "';"
   END

ON LAST ROW
   SKIP 1 LINE
   PRINT "COMMIT WORK;"
   SKIP 1 LINE
   PRINT "{ End Of SQL File }"
END
Tags: