Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mattt
Posting Yak Master
194 Posts |
 Posted - 2010-12-31 : 05:37:38
|
| Hi,Bit of an odd one this. I can't post much code as the whole sproc is far too long, complex and meaningless out of context so no-one might be able to help - but I'd appreciate it if you could look and see.My query currently contains an insert into a temp table from a select statement. The select - and insert - includes an Id column from a left outer joined table. What I'm looking to have happen is to *only* select records where this Id column has a null value.To ensure this happens I've got as the last part of my select the statement "and joinedTable.Id is null". However when this is added in to the query, rows that I expect to be excluded turn up as part of the select with a joinedTable.Id of null.However if I remove the "and joinedTable.Id is null" statement from the query the surprising - and annoying - thing is that the rows I was expecting to be excluded actually turn up with a value in joinedTable.Id!I don't really understand how adding the "and joinedTable.Id is null" statement seems to allow the select statement to change the expected value for that column from an Id to a null and include the row. Can anyone make some generic suggestions as to why this might be happening and what I can do to debug it?Cheers,Matt |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-31 : 05:53:26
|
| >> and joinedTable.Id is nullis that in the join or the where clause?==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
mattt
Posting Yak Master
194 Posts |
Posted - 2010-12-31 : 06:00:28
|
quote:
Originally posted by nigelrivett
>> and joinedTable.Id is nullis that in the join or the where clause?
Good catch, thanks. It did indeed need to be in a where clause in order to work. This is what happens when you let procedural programmers write SQL :) |
|
|
|
|
|
|
Insert seems to bypass null values