Retrieving Mutually Exclusive Records using Seagate Crystal Reports

• Download demo - 41.9 Kb
• Download source - 32.3 Kb

Introduction

I had a requirement in my application to retrieve mutually exclusive or disjoint sets of data into our system. Two sets A and B are said to be mutually exclusive or disjoint if they have nothing in common. Our system later would execute these queries to retrieve disjoint data.

Background

Data Retrieval and Manipulation in SQL

There are three different but related forms of relational calculus: tuple calculus, domain calculus, and transform languages. SQL is based on the transform language SEQUEL.

Commands in SQL specify in a specific syntax which columns to manipulate, from what tables, and for what rows.

Basic Data Retrieval and Manipulation in SQL

SQL data retrieval statements include the following three distinct clauses:

Example Database

Before getting into the details, let me illustrate it with a simple example. Suppose that we have the following database:

There are a few remarks to make about it:

• All tables have primary keys.
• PAYMENT_DETAIL has a multiple field or composite primary key.
• BILL_PAYMENT.PAYMENT_MODE contains either a value Cash (0) or a value Other (1).
• Data is stored in PAYMENT_DETAIL table, if and only if BILL_PAYMENT.PAYMENT_MODE value is Other.
• The data is divided into two mutually exclusive or disjoint sets based on the value of BILL_PAYMENT.PAYMENT_MODE.

• We connect to the database using ODBC.
• The User DSN is Bills.

Table Operations

In a relational system, data from related tables are combined into one table (or view), and then displayed, or used as input to a form or report definition. Thus, the majority of relational database programming involves combining into one table, data from two, three, or more related tables.

Retrieving Mutually Exclusive Records

Once the database is constructed, now is the time to retrieve records from the database. The simplest solution will be:

SELECT BP.BILL_ID, BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT, 
                   BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
    (
        SELECT PD.DESCRIPTION
        FROM PAYMENT_DETAIL AS PD
        WHERE 
            PD.BILL_ID = BP.BILL_ID
            AND
            PD.INSTALLMENTNO = BP.INSTALLMENTNO
            AND
            BP.PAYMENT_MODE = 1
    )
FROM BILL_PAYMENT AS BP
WHERE
    BP.BILL_ID IN     (
                SELECT BI.BILL_ID
                FROM BILL_INFO AS BI
            );

In the preceding subquery solution, it was necessary to examine the second inner query before considering the outer query. That is, the result of the inner query is used to limit the processing of the outer query. In contrast, for other kinds of queries, called correlated subqueries, such as the first inner query, the processing of the inner query depends on data from the other query.

Correlated subqueries may seem difficult to write at first, but once you understand that your query needs to process one table for each row of another table, the use of correlated subqueries becomes clear. Sometimes, depending on how you conceive a query, you can answer the same question by either non-correlated or correlated subqueries or even without using subqueries. Unfortunately, that's what we shall do here. Because, the encircled column in the following snapshot is actually an expression involving column. And Seagate Crystal Reports 8.0 doesn't allow us to use expressions involving columns as fields in a report. Although Seagate Crystal Reports 8.0 supports a special field called the "Formula Field" which allows expressions involving columns, it doesn't allow the use of SQL in Formula Field, which is our requirement here.

Power of SQL

Because relational query languages like SQL are set-oriented languages (that is, commands operate on and generate sets of rows), the equivalent of various set operations may also be available.

Appending Query Results Together

The UNION command combines the result of two queries into one table as long as the two tables being combined have compatible corresponding columns. That is, the two tables must have the same number of columns, and the corresponding columns must have the same data type. The results from multiple queries may be combined by inserting UNION between each query.

• SQL

  SQL

  (
      SELECT BI.BILL_ID, BI.ISSUE_DATE, BI.CLIENT_NAME, 
          BI.TOTAL_AMOUNT, BI.DISCOUNT, BI.BALANCE,
          BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT, 
          BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
          PD.DESCRIPTION
      FROM     (
              BILL_INFO AS BI INNER JOIN BILL_PAYMENT 
              AS BP ON BI.BILL_ID = BP.BILL_ID
          ) 
          INNER JOIN 
              PAYMENT_DETAIL AS PD ON BP.BILL_ID = PD.BILL_ID 
              AND 
              BP.INSTALLMENTNO = PD.INSTALLMENTNO
      WHERE 
          BP.PAYMENT_MODE = 1 
          AND 
          PD.BILL_ID = BP.BILL_ID 
          AND 
          PD.INSTALLMENTNO = BP.INSTALLMENTNO
      ORDER BY 
          BI.BILL_ID ASC, BI.ISSUE_DATE ASC
  )
  UNION
  (
      SELECT BI.BILL_ID, BI.ISSUE_DATE, BI.CLIENT_NAME, 
          BI.TOTAL_AMOUNT, BI.DISCOUNT, BI.BALANCE,
          BP.INSTALLMENTNO, BP.INSTALLMENT_AMOUNT, 
          BP.INSTALLMENT_PAID_DATE, BP.PAYMENT_MODE,
          ''
      FROM BILL_INFO AS BI, BILL_PAYMENT AS BP
      WHERE 
          BP.PAYMENT_MODE = 0 
          AND 
          BI.BILL_ID = BP.BILL_ID
      ORDER BY BI.BILL_ID ASC, BI.ISSUE_DATE ASC
  }

• C++

  CString Query1, Query2;
  
  Query1 = "SELECT
          BI.`BILL_ID`, BI.`ISSUE_DATE`, BI.`CLIENT_NAME`, 
               BI.`TOTAL_AMOUNT`, BI.`DISCOUNT`, BI.`BALANCE`, 
          BP.`INSTALLMENTNO`, BP.`INSTALLMENT_AMOUNT`, 
               BP.`INSTALLMENT_PAID_DATE`, BP.`PAYMENT_MODE`,
          PD.`DESCRIPTION`
       FROM
          (`BILL_INFO` BI INNER JOIN 
          `BILL_PAYMENT` BP ON BI.`BILL_ID` = BP.`BILL_ID`) INNER JOIN
          `PAYMENT_DETAIL` PD ON BP.`BILL_ID` = PD.`BILL_ID` 
                AND BP.`INSTALLMENTNO` = PD.`INSTALLMENTNO`
       WHERE
          BP.`PAYMENT_MODE` = 1 AND PD.`BILL_ID` = 
               BP.`BILL_ID` AND PD.`INSTALLMENTNO` = BP.`INSTALLMENTNO`
       ORDER BY 
          BI.`BILL_ID` ASC, BI.`ISSUE_DATE` ASC";
  
  Query2 = "SELECT
          BI.`BILL_ID`, BI.`ISSUE_DATE`, BI.`CLIENT_NAME`, 
               BI.`TOTAL_AMOUNT`, BI.`DISCOUNT`, BI.`BALANCE`, 
          BP.`INSTALLMENTNO`, BP.`INSTALLMENT_AMOUNT`, 
               BP.`INSTALLMENT_PAID_DATE`, BP.`PAYMENT_MODE`,
          ''
       FROM 
          BILL_INFO BI, BILL_PAYMENT BP
       WHERE 
          BP.`PAYMENT_MODE` = 0 AND BI.`BILL_ID` = BP.`BILL_ID`
       ORDER BY BI.`BILL_ID` ASC, BI.`ISSUE_DATE` ASC";
  
  CString Query = Query1+" UNION "+Query2;
  
  m_CrystalReport.SetReportFileName("Report.rpt");
  m_CrystalReport.SetWindowTitle("Retrieving Mutually Exclusive 
                        Records with Seagate Crystal Reports");
  m_CrystalReport.SetSQLQuery(Query);
  m_CrystalReport.PrintReport();

The encircled portion of the report shows, that is just about it. Hope it helps some of you folks out there.