Click here to Skip to main content
Click here to Skip to main content
Go to top

Retrieving Mutually Exclusive Records using Seagate Crystal Reports

, 12 Aug 2004
Rate this:
Please Sign up or sign in to vote.
An article explaining the retrieval of mutually exclusive records using Crystal Reports with VC++, and unleashing the problems with solutions.

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:

SELECT

Lists the columns (including expressions involving columns) from base tables or views to be projected into the tables that will be the result of the command.

FROM

Identifies the tables or views from which columns will be chosen to appear in the result table, and includes the tables or views needed to join tables to process the query.

WHERE

Includes the conditions for row selection within a single table or view, and the conditions between tables or views for joining.

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

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 Pinmemberali_gooly200116-Apr-11 23:47 
Generalhello......again Pinmemberlallaba5-Dec-05 0:37 
Generalplz help ..... Pinmemberlallaba30-Nov-05 1:00 
GeneralRe: plz help ..... PinmemberMuneeb Ahmed Awan2-Dec-05 19:19 
GeneralRe: plz help ..... PinmemberMuneeb Awan7-Dec-05 20:16 
Generalplease i need help.... Pinmemberlallaba12-Nov-05 23:35 
GeneralRe: please i need help.... PinmemberMuneeb Ahmed Awan18-Nov-05 19:35 
GeneralcRYSTAL REPORT NOT REFERESHING PinmemberShanmuga Sundar5-May-05 0:04 
GeneralRe: cRYSTAL REPORT NOT REFERESHING PinsussAnonymous6-May-05 2:44 
Questionhow to get seagate reports into crystal? PinsussAnonymous25-Apr-05 23:54 
AnswerRe: how to get seagate reports into crystal? PinsussAnonymous29-Apr-05 18:24 
Generalswitch between db servers PinmemberTomSyk19-Mar-05 13:26 
GeneralHelp me quering another file Pinmemberop_oap3-Mar-05 16:44 
GeneralRe: Help me quering another file PinmemberMuneeb Ahmed Awan3-Mar-05 17:22 
GeneralRe: Help me quering another file Pinmemberop_oap6-Mar-05 16:29 
GeneralRe: Help me quering another file PinmemberMuneeb Ahmed Awan6-Mar-05 17:14 
GeneralRe: Help me quering another file Pinmemberop_oap10-Mar-05 16:33 
GeneralNeed help! PinmemberBlackDice19-Nov-04 10:57 
GeneralRe: Need help! PinmemberMuneeb Ahmed Awan20-Nov-04 17:14 
GeneralCannot run in Visual c++.NET PinmemberBahrudin Hrnjica19-Nov-04 7:00 
GeneralMutiple line PinmemberSébastien Lorion18-Aug-04 13:45 
GeneralFacing problem in Crystal report Pinmemberraghute_h15-Aug-04 22:42 
GeneralRe: Facing problem in Crystal report PinmemberMax Santos25-Aug-04 1:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 13 Aug 2004
Article Copyright 2004 by Muneeb Ahmed Awan
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid