August 18, 2008 at 9:31 am
I have run into an issue that I was hoping somebody could help with. I am trying to run this query against SQL Server 2000, but it seems to be ignoring the first "Is Null" in my where clause. The query joins to a table in another database:
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey
Left Outer Join ConversionDB..RpBill R On
R.RbBillYear = Cast(Left(V.ArVSourceId, 4) As Int) And
R.RbBillNo = Cast(SubString(V.ArVSourceId, 6, 8) As Int) And
R.RbInstNo = Cast(Right(V.ArVSourceId, 2) As Int)
Where L.BdKey Is Null And
V.ArVType = 9 And
R.RpNumber Is Null
This query returns the following results:
BdKey
-----------
6107745
6108245
6108545
etc....
It seems to be flat-out ignoring the "L.BdKey Is Null" in my where clause.
If I take out the join to the table in the other database, it works fine:
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey
Where L.BdKey Is Null And
V.ArVType = 9
This query returns the following results:
BdKey
-----------
NULL
NULL
NULL
NULL
etc...
Please help (and forgive me if this is a widely known issue)
Thanks,
Reid
August 18, 2008 at 10:22 am
Learn you JOINs
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey
And V.ArVType = 9
Where L.BdKey Is Null
N 56°04'39.16"
E 12°55'05.25"
August 18, 2008 at 10:35 am
I'm not sure what "Learn you JOINs" means, but I do prefer the changes you made to the join. That being said, the results should come out the same.
This query should have the same results...
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey
Where L.BdKey Is Null And
V.ArVType = 9
as your query:
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey And
V.ArVType = 9
Where L.BdKey Is Null
When I applied your suggestion to the query I am actually having trouble with, I still get the strange results:
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey And
V.ArVType = 9
Left Outer Join ConversionDB..RpBill R On
R.RbBillYear = Cast(Left(V.ArVSourceId, 4) As Int) And
R.RbBillNo = Cast(SubString(V.ArVSourceId, 6, 8) As Int) And
R.RbInstNo = Cast(Right(V.ArVSourceId, 2) As Int)
Where L.BdKey Is Null And
R.RpNumber Is Null
This query still returns the following results:
BdKey
-----------
6107745
6108245
6108545
etc....
Thanks for the suggestion though.
August 18, 2008 at 10:38 am
that's interesting...I'm guessing that the joins to the RPBILL table is doing the wierdness...
if the two tables are in a sub select like below, what are the results?
[font="Courier New"]SELECT L.BdKey
FROM ArLine L
INNER JOIN
(
SELECT V.ArVKey
FROM ArInvoice V
LEFT OUTER JOIN ConversionDB..RpBill R
ON R.RbBillYear = CAST(LEFT(V.ArVSourceId, 4) AS INT)
AND R.RbBillNo = CAST(SUBSTRING(V.ArVSourceId, 6, 8) AS INT)
AND R.RbInstNo = CAST(RIGHT(V.ArVSourceId, 2) AS INT)
WHERE V.ArVType = 9
AND R.RpNumber IS NULL
) X ON L.ArVKey = X.ArVKey
WHERE L.BdKey IS NULL
[/font]
Lowell
August 18, 2008 at 10:51 am
Your query gave me the same results.
One other interesting thing that I didn't mention before is that changing the "L.BdKey Is Null" to "IsNull(L.BdKey,-1) = -1" actually gives me the results I am looking for.
This query gives me different results than the one below it. Shouldn't these two queries produce the same results (assuming of course that -1 is not a possible value for the BdKey column)?:
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey And
V.ArVType = 9
Left Outer Join ConversionDB..RpBill R On
R.RbBillYear = Cast(Left(V.ArVSourceId, 4) As Int) And
R.RbBillNo = Cast(SubString(V.ArVSourceId, 6, 8) As Int) And
R.RbInstNo = Cast(Right(V.ArVSourceId, 2) As Int)
Where IsNull(L.BdKey,-1) = -1 And
R.RpNumber Is Null
Select L.BdKey
From ArLine L
Join ArInvoice V On
L.ArVKey = V.ArVKey And
V.ArVType = 9
Left Outer Join ConversionDB..RpBill R On
R.RbBillYear = Cast(Left(V.ArVSourceId, 4) As Int) And
R.RbBillNo = Cast(SubString(V.ArVSourceId, 6, 8) As Int) And
R.RbInstNo = Cast(Right(V.ArVSourceId, 2) As Int)
Where L.BdKey Is Null And
R.RpNumber Is Null
Thanks
Reid
August 18, 2008 at 1:32 pm
i wonder if doing SET ANSI_NULLS ON, running the statement, and then SET ANSI_NULLS OFF would give different results. I haven't been able to duplicate, but that's wierd;
Lowell
August 18, 2008 at 1:52 pm
The query returns the same results regardless of whether ANSI_NULLS is on or off.
Thanks,
Reid
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply