Q

Invoice amounts in a Crystal report

Hi. Is there a way to make a sum of several fields in a table and compare it with another value? Something like

the following with the WHERE instruction (this is in Crystal Reports V8.0) --

SELECT Invoice1."IDInvoice", Invoice1."Date"
     , Invoice1."CustomerName", Invoice1."SubTotal"
     , Invoice1."IVA", Invoice1."IDCustomer", Invoice1."Paid"
     , Invoice1."InvoiceNumber", Invoice1."ExtraCharge"
     , Customer1."CustomerName", Customer1."Companyname"
     , Customer1."MidleName", Customer1."LastName"
  FROM "PUB"."Invoice" Invoice1 
 INNER JOIN 
       "PUB"."Customer" Customer1 
    ON Invoice1."IDCustomer" = Customer1."IDCustomer" 
  LEFT JOIN
       "PUB"."DetDocumentCustomer" DetDocumentCustomer1 
    ON Invoice1."IDBranch" = DetDocumentCustomer1."IDBranch" 
   AND Invoice1."IDInvoice" = DetDocumentCustomer1."IDInvoice" 
 WHERE ( DetDocumentCustomer1."StatusItem" IS NULL 
      OR DetDocumentCustomer1."StatusItem" <> 4 
      OR Invoice1."SubTotal" + Invoice1."IVA" 
         + Invoice1.ExtraCharge - Invoice1."Paid" > 0
       )
ORDER BY Invoice1."CustomerName" ASC

The syntax sounds okay in Crystal (it does not complain at all) but the report in Crystal still shows records with --

Invoice1."SubTotal" + Invoice1."IVA" 
+ Invoice1.ExtraCharge - Invoice1."Paid" = 0

Is something wrong with my syntax?


Yes and no. Yes, there's something wrong, but no, your syntax is fine. A syntax error would prevent the query from running at all. The fact that it's running but returning the wrong results is indicative of a semantic error. Crystal and similar tools don't usually catch semantic errors.

I think your second OR should be an AND, and there should be another level of parentheses around the conditions being ORed --

 WHERE ( ( DetDocumentCustomer1."StatusItem" IS NULL 
        OR DetDocumentCustomer1."StatusItem" <> 4 
         ) 
     AND Invoice1."SubTotal" + Invoice1."IVA" 
         + Invoice1.ExtraCharge - Invoice1."Paid" > 0
       )

Note you can remove the outer parentheses, although some tools might stick them back in (they don't hurt unless they are semantically wrong!) --

 WHERE ( DetDocumentCustomer1."StatusItem" IS NULL 
      OR DetDocumentCustomer1."StatusItem" <> 4 
       ) 
   AND Invoice1."SubTotal" + Invoice1."IVA" 
       + Invoice1.ExtraCharge - Invoice1."Paid" > 0

The parentheses around the ORed conditions are important, because AND conditions are evaluated ahead of ORs. For example,

  A or B and C

will be evaluated as

  A or ( B and C ) 

What you had is

  A or B or C 

while what you want is

  ( A or B ) and C 

For More Information

  • What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
  • The Best SQL Web Links: tips, tutorials, scripts, and more.
  • Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

This was first published in March 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close