Roy Harvey
3/20/2007 4:19:00 PM
The test WHERE (H.IATRCD = 'R') is eliminating the OUTER cases, as the
NULLs for the OUTER rows do not pass the test.
The usual approach is to move this test to the ON clause of that
table's OUTER JOIN.
Also, TOP (100) PERCENT is a bad idea and should be removed. It does
not change the results, and the one purpose it ever had was an
unreliable, undocumented and unsupported way to try to "order" a view
that completely broke down in SQL Server 2005.
SELECT I.IBITNO, I.IBITCL, I.IBITSC,
I.OHQ, I.AvgCost,
ISNULL(dbo.fnConvertDate(H.IATRDT), '2001-12-31') AS TDate,
ISNULL(H.IATRQT, 0) AS IATRQT
FROM dbo.vwInventoryAgingI AS I
LEFT OUTER
JOIN dbo.IAHST AS H
ON LTRIM(RTRIM(I.IBITNO)) = LTRIM(RTRIM(H.IAITNO))
AND (H.IATRCD = 'R')
WHERE I.IBITCL = '90'
ORDER BY I.IBITNO, H.IATRDT DESC
Roy Harvey
Beacon Falls, CT
On Tue, 20 Mar 2007 11:03:11 -0500, "Ross Culver"
<rculver@warrenalloy.com> wrote:
>Here's a simple query:
>
>SELECT TOP (100) PERCENT I.IBITNO, I.IBITCL, I.IBITSC, I.OHQ, I.AvgCost, ISNULL(dbo.fnConvertDate(H.IATRDT), '2001-12-31') AS TDate, ISNULL(H.IATRQT, 0) AS IATRQT
>FROM dbo.vwInventoryAgingI AS I LEFT OUTER JOIN
>
>dbo.IAHST AS H ON LTRIM(RTRIM(I.IBITNO)) = LTRIM(RTRIM(H.IAITNO))
>
>WHERE (H.IATRCD = 'R') and I.IBITCL = '90'
>
>ORDER BY I.IBITNO, H.IATRDT DESC
>
>The left-hand side should return 3483 records with only 597 matching records on the right-hand side, but instead, the query only returns the 597 records.
>
>Why all 3483 records not returned with the 597 corresponding fields and 2886 records with the substitute for null values? (Using SQL 2005)
>
>Note: I've tried taking out the order by and top 100%, but that didn't make any difference.
>
>Thanks, Ross.