View Incident Report

Serious

Reported By: Rick Dement
Reported On: 10/10/2005
For: Version 4.21 Build 10

# 2118 Using an IN Expression within a LEFT OUTER JOIN Clause Causes Incorrect Results

This first statement returns the correct results with the information for the invntry and bininv tables returned correctly. I thought I would clean up the statements a bit and use IN. However, when I do this I get the same number of records but no correctly joined invntry or bininv tables. The fields are all blank for these two tables

SELECT * 
FROM 
 Invoice I JOIN IDetail D ON (I."Unique ID" = D.InvoiceNumber)
 LEFT OUTER JOIN Invntry S ON ((D.InventoryType = 34 OR
D.InventoryType = 44) AND CAST(S."Unique ID" AS VARCHAR(15)) =
D.ItemNumber)
 LEFT OUTER JOIN BinInv B ON ((D.InventoryType = 51 OR D.InventoryType
= 57) AND CAST(B."Unique ID" AS VARCHAR(15)) = D.ItemNumber)

SELECT * 
FROM 
 Invoice I JOIN IDetail D ON (I."Unique ID" = D.InvoiceNumber) 
 LEFT OUTER JOIN Invntry S ON (D.InventoryType IN (34, 44) AND
CAST(S."Unique ID" AS VARCHAR(15)) = D.ItemNumber)
 LEFT OUTER JOIN BinInv B ON (D.InventoryType IN (51, 57) AND
CAST(B."Unique ID" AS VARCHAR(15)) = D.ItemNumber)

Resolution

Fixed Problem on 10/15/2005 in version 4.21 build 11

Products Affected

DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap