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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy