PRODUCT   :  R:BASE                  VERSION      :  3.1
   From Bill Downall, 5411 White Willow Court, Indianapolis, IN 46254-
   9633. Bill is a database consultant and educator. You can reach him at
   317-297-3810, or through David M. Blocker and Associates, at 617-784-
   The January/February 1991 R:BASE EXCHANGE showed you one technique for
   producing an aged accounts receivable report--or any report that needs
   to print summaries of several rows horizontally across the page. This
   article teaches you how to use SQL to do the same thing.
   SQL Technique Overview
   In this article, you'll use the following SQL techniques to prepare
   the data:
     o  SUM function (one of several SELECT functions) and logical (IFGT
        and IFLT) SuperMath functions--you'll use these to create totals
        of numeric columns or expressions across a group of rows. SUM
        gets the totals while IFGT and IFLT create the groups (print
        columns) that will be shown horizontally across the page. The
        IFGT and IFLT expressions used here are similar to those given in
        the previous article.
     o  GROUP BY clause of the SELECT command--you'll use GROUP BY to
        repeat the same calculations for each client account number.
     o  Views--you'll use views to save complex SELECT commands and to
        give descriptive, meaningful names to the expressions--names that
        will appear in subsequent SELECT commands or in the output.
   The example described below uses two views. The first view creates an
   expression and gives it a column name. The second view names that
   pseudo-column within an expression. After creating the views, you'll
   use a single SQL SELECT to create the aging report.
   Example Table & Data
   An aged accounts receivable report shows the ages (in days) of amounts
   that have been billed but not yet paid.
   To make comparison with the previous article easy, I based this SQL
   technique on the same table (ARITEMS) and columns: ACCT (account
   number), AMOUNT (amount owed), and TDATE (transaction date). Create 
   the database (AGING) at the Create/Modify menu with one table named
   (ARITEMS), with three columns.  CREATE TABLE ARITEMS (ACCT integer,
   AMOUNT currency, TDATE date) and to load ARITEMS with
   You will create two views as explained below.
   Step by Step to the Aging Report
   STEP 1
   Create the first view (AGING). The variable VREPDATE holds the report
   date (as of date). You must set it before creating the view. For
   example, you might set it by using this command:
     SET VAR vrepdate DATE = ('12/31/90')
   Create the view by using this command:
     CREATE VIEW aging +
       ( client, amount, daysout ) AS SELECT +
       acct, amount, (.vrepdate - tdate) +
       FROM aritems
   The view AGING is slightly different from the table ARITEMS. In place
   of the transaction date column (TDATE), this view creates a new
   pseudo-column (DAYSOUT), an INTEGER defined as the numbers of days
   between the report date and transaction date.
   The expression that defines DAYSOUT uses the variable VREPDATE. You
   could use .#DATE (the current system date) instead of .VREPDATE, but
   then you couldn't specify a different report date (ending date or "as
   of" date). For example, #DATE is inappropriate for the example data.
   The example rows are all dated between May and December 1990.
   Therefore, for this example, you need to set VREPDATE to December 31,
   The view definition contains a variable (VREPDATE), so before using
   the view, give VREPDATE an initial data type and value. Test the view
   by entering this command:
     SELECT * FROM aging
   The first few lines of output look like this:
   client     amount          daysout
   ---------- --------------- ---------
          101          $73.00       225
          102         $146.00       195
          103         $219.00       213
          104         $292.00       140
          101          $22.00       179
   STEP 2
   Create the second view (AGEGROUP) to create and sum the report columns
   by using the following command:
     CREATE VIEW agegroup +
       (Client, Total, Current_, Over_30, +
       Over_60, Over_90, Over_120) +
       AS SELECT Client, SUM (amount), +
         SUM (IFLT(daysout,30,amount,0)), +
         SUM (IFGT(daysout,29, +
           (IFLT(daysout,60,amount,0)),0)), +
         SUM (IFGT(daysout,59,+
           (IFLT(daysout,90,amount,0)),0)), +
         SUM (IFGT(daysout,89,+
           (IFLT(daysout,120,amount,0)),0)), +
         SUM (IFGT(daysout,119,amount, 0)) +
         FROM aging GROUP BY client
   Notice how this view's column structure is similar to that of an aging
   report. The column names created in the view will appear as column
   headings in a SELECT command. You can't name a column 30_to_60 because
   SQL naming conventions require all column names to begin with a
   letter, and you can't name a column CURRENT because that's a reserved
   word. The underscore character at the end of CURRENT_ makes it
   Look at the expression in the definition for the CURRENT_ view column:
     SUM (IFLT(daysout,30,amount,0))
   In the view AGING, the column DAYSOUT was defined as the number of
   days between billing and the report date. Now the SUM(IFLT(...))
   expression calculates a zero for any row where DAYSOUT is greater than
   or equal to 30. If DAYSOUT is less than 30, the value of the AMOUNT
   column is used for the CURRENT_ column in this second view.
   Across the entire view, the SELECT function SUM calculates the total.
   For example, look at this portion:
     SUM (IFLT(daysout,30,amount,0))
   It calculates the sum of the AMOUNT column for those rows that are
   current (less than 30 days old). If that was the only number you cared
   about, it would be easier to get with the following WHERE clause:
     SELECT SUM (amount) FROM aritems +
       WHERE daysout <= 30
   But each SELECT command has only one WHERE clause. In order to build a
   single SELECT command that shows one sum for current rows and
   different sums for other age groups, you need to use the IF
   expressions to do the work of an internal WHERE clause.
   SELECT uses the IF expressions to process all the rows in the view,
   putting each one in the correct report column grouping and summing
   each group.
   There's a SUM function for each of the five aging periods. Several of
   them use complex IFGT and IFLT functions. For example, look at this
   SUM function:
     SUM (IFGT(daysout,29,+
   The third argument of the IFGT function is another IF function. In
   English, it says, "If DAYSOUT is greater than 30, see if it's also
   less than 60. If it is, add the value of the AMOUNT column. Otherwise,
   add zero."
   STEP 3
   To produce the final report, run a short command file similar to
   AGING.CMD (listed below). It initializes VREPDATE with the "as of"
   date, and uses a single SELECT based on the second view (AGEGROUP) to
   print the report shown below. The SELECT uses "=w" width specifiers to
   format the report columns and "=S" to provide grand totals. The
   capitalization used for column names in this SELECT is what shows up
   in the final report. The following AGING.CMD is an example:
     *( AGING.CMD--produced aging report.)
     SET VAR vrepdate DATE = ('12/31/90')
     FILLIN vrepdate=8 USING +
       'Enter report date: ' EDIT REVERSE
     *( Insert commands to select print destination)
     WRITE '                  Aged +
     Accounts Receivable Report:' .vrepdate
     WRITE ' ' ; WRITE ' '
     SELECT Client=6, Total=10=S, +
       Current_=10=S, Over_30=10=S, +
       Over_60=10=S, Over_90=10=S, +
       Over_120=10=S FROM agegroup
     CLEAR VAR vrepdate
   STEP 4
   Run AGING.CMD and create the report. Here's an example of the output:
                 Aged Accounts Receivable Report:  12/31/90
    Client Total      Current_  Over_30   Over_60   Over_90   Over_120
    ------ ---------- --------- --------- --------- --------- ----------
       101    $178.00     $0.00     $0.00     $0.00     $0.00    $178.00
       102  $1,581.85     $0.00   $324.85     $0.00     $0.00  $1,257.00
       103  $1,466.85   $470.85   $219.00     $0.00   $329.00    $448.00
       104  $2,094.85   $908.85   $292.00   $479.00   $123.00    $292.00
    ------ ---------- --------- --------- --------- --------- ----------
            $5,321.55 $1,379.70   $835.85   $479.00   $452.00  $2,175.00
   Further Enhancements
   To improve the look of the report, set HEADINGS OFF, and use a few
   WRITE statements to create your own headings.
   To suppress printing zero dollar values, create an R:BASE report
   instead of using the final SELECT. Base the report on AGEGROUP, and
   use IF expressions to convert the numbers to TEXT values, replacing
   the $0.00 values with blanks. Locate the TEXT values on detail (D)
   The view (AGEGROUP) groups the data, and the report prints it. There
   will be a longer delay before the report starts to print because the
   view must be assembled. But once printing begins, all the summary
   totals have already been calculated, so R:BASE has fewer expressions
   to calculate while the report prints.