March 16, 2005 at 12:12 pm
Hello,
I don't understand why this statement behaves as it does. I've isolated this SELECT statement because I identified it as the reason why my INSERT statement isn't working:
When I run it commenting out the WHERE criteria for the joined table (but including the fields to I can check the values) I get this:
SELECT DI.ACCOUNT, DI.[BILL NO], DI.RptDate, DI.HOLDS, DI.[CLM CHK GRP],HS.Account,HS.BillNo,HS.RptDate,HS.Hold
FROM brenna.DNFBImport DI
LEFT JOIN brenna.HoldStore HS ON DI.ACCOUNT = HS.Account AND DI.[BILL NO] = HS.BillNo AND DI.RptDate = HS.RptDate
WHERE
--HS.Account=Null AND HS.BillNo=Null AND HS.RptDate=Null AND HS.Hold=Null And
DI.Account = 'H27393149'
Here are the results. The values in the joined table, HS.Account AND HS.BillNo AND HS.RptDate AND HS.Hold are all NULL.
H27393149 1 3/14/05 INS2EMP ADM NULL NULL NULL NULL
But when I add the criteria HS.Account=Null AND HS.BillNo=Null AND HS.RptDate=Null AND HS.Hold=Null like this:
SELECT DI.ACCOUNT, DI.[BILL NO], DI.RptDate, DI.HOLDS, DI.[CLM CHK GRP],HS.Account,HS.BillNo,HS.RptDate,HS.Hold
FROM brenna.DNFBImport DI
LEFT JOIN brenna.HoldStore HS ON DI.ACCOUNT = HS.Account AND DI.[BILL NO] = HS.BillNo AND DI.RptDate = HS.RptDate
WHERE HS.Account=Null AND HS.BillNo=Null AND HS.RptDate=Null AND HS.Hold=Null And
DI.Account = 'H27393149'
I get no rows returned. Will you please explain why this is happening and how I can prevent it?
Thank you.
March 16, 2005 at 12:17 pm
IS NULL
not = NULL
Using Left Joined columns in the WHERE clause converts the Join to an Inner join. An Inner Join, combined with the error of using "= Null" instead of "IS NULL" means no records get returned.
March 16, 2005 at 1:53 pm
Thank you!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply