Click here to Skip to main content
13,352,280 members (67,375 online)
Click here to Skip to main content
Add your own
alternative version

Stats

84K views
1.4K downloads
22 bookmarked
Posted 12 Aug 2004

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

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMessage Closed Pin
17-Apr-11 0:47
memberali_gooly200117-Apr-11 0:47 
Generalhello......again Pin
lallaba5-Dec-05 1:37
memberlallaba5-Dec-05 1:37 
Generalplz help ..... Pin
lallaba30-Nov-05 2:00
memberlallaba30-Nov-05 2:00 
GeneralRe: plz help ..... Pin
Muneeb Ahmed Awan2-Dec-05 20:19
memberMuneeb Ahmed Awan2-Dec-05 20:19 
GeneralRe: plz help ..... Pin
Muneeb Awan7-Dec-05 21:16
memberMuneeb Awan7-Dec-05 21:16 
Generalplease i need help.... Pin
lallaba13-Nov-05 0:35
memberlallaba13-Nov-05 0:35 
GeneralRe: please i need help.... Pin
Muneeb Ahmed Awan18-Nov-05 20:35
memberMuneeb Ahmed Awan18-Nov-05 20:35 
GeneralcRYSTAL REPORT NOT REFERESHING Pin
Shanmuga Sundar5-May-05 1:04
memberShanmuga Sundar5-May-05 1:04 
GeneralRe: cRYSTAL REPORT NOT REFERESHING Pin
Anonymous6-May-05 3:44
sussAnonymous6-May-05 3:44 
Questionhow to get seagate reports into crystal? Pin
Anonymous26-Apr-05 0:54
sussAnonymous26-Apr-05 0:54 
AnswerRe: how to get seagate reports into crystal? Pin
Anonymous29-Apr-05 19:24
sussAnonymous29-Apr-05 19:24 
Generalswitch between db servers Pin
TomSyk19-Mar-05 14:26
memberTomSyk19-Mar-05 14:26 
GeneralHelp me quering another file Pin
op_oap3-Mar-05 17:44
memberop_oap3-Mar-05 17:44 
GeneralRe: Help me quering another file Pin
Muneeb Ahmed Awan3-Mar-05 18:22
memberMuneeb Ahmed Awan3-Mar-05 18:22 
GeneralRe: Help me quering another file Pin
op_oap6-Mar-05 17:29
memberop_oap6-Mar-05 17:29 
GeneralRe: Help me quering another file Pin
Muneeb Ahmed Awan6-Mar-05 18:14
memberMuneeb Ahmed Awan6-Mar-05 18:14 
GeneralRe: Help me quering another file Pin
op_oap10-Mar-05 17:33
memberop_oap10-Mar-05 17:33 
GeneralNeed help! Pin
BlackDice19-Nov-04 11:57
memberBlackDice19-Nov-04 11:57 
GeneralRe: Need help! Pin
Muneeb Ahmed Awan20-Nov-04 18:14
memberMuneeb Ahmed Awan20-Nov-04 18:14 
GeneralCannot run in Visual c++.NET Pin
Bahrudin Hrnjica19-Nov-04 8:00
memberBahrudin Hrnjica19-Nov-04 8:00 
GeneralMutiple line Pin
Sébastien Lorion18-Aug-04 14:45
memberSébastien Lorion18-Aug-04 14:45 
GeneralFacing problem in Crystal report Pin
raghute_h15-Aug-04 23:42
memberraghute_h15-Aug-04 23:42 
GeneralRe: Facing problem in Crystal report Pin
Max Santos25-Aug-04 2:59
memberMax Santos25-Aug-04 2:59 

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

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

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