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?
Requires Free Membership to View
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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation