View Incident Report

Serious

Reported By: Phillip Koebbe
Reported On: 4/26/2001
For: Version 2.08 Build 1

# 772 Using a Nested INNER JOIN within a LEFT OUTER JOIN in a SQL Query Does Not Work Properly

The following query does not work. When the fees SalesTax and PrepFee do actually exist, it works okay. But, when they do not exist, the query returns all fees that exist for that specific transaction, which means multiple rows in the result set. Basically what's happening is that when the INNER JOIN to FeeType is successful, only that record is returned. But, when that INNER JOIN fails,
the query then reverts back to the LEFT OUTER JOIN as if the INNER JOIN wasn't there at all.

SELECT T.TransactionID, ST.Amount AS SalesTax, PF.Amount AS PrepFee
FROM CFM_Transaction T
LEFT OUTER JOIN Fee ST ON ST.TransactionID = T.TransactionID
INNER JOIN FeeType STT ON STT.FeeTypeID = ST.FeeTypeID 
  AND STT.FeeType = 'Sales Tax'
LEFT OUTER JOIN Fee PF ON PF.TransactionID = T.TransactionID
INNER JOIN FeeType PFT ON PFT.FeeTypeID = PF.FeeTypeID 
  AND PFT.FeeType = 'Prep Fee'

Comments and Workarounds

The problem was in the join optimization - the optimizer was trying to flip around the INNER JOIN when it shouldn't have been due to the presence of the driving LEFT OUTER JOIN above it in the nesting. The workaround was to add a NOJOINOPTIMIZE clause at the end of the SQL statement.

Resolution

Fixed Problem on 4/30/2001 in version 2.09 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap