question

george.greiner avatar image
george.greiner asked

Exists Function problem

Hello,

I have an issue regarding the Exists function with the code below:

SELECT l.street, l.Number, l.Direction, l.suffix, l.Unit, l.LienNumber, l.LienType,                     
l.DateField, l.Amount                    
FROM Localities AS l                    
WHERE (((Not Exists (SELECT *                    
FROM vw_BRT                    
WHERE street = l.street AND streetNum = l.Number))));

For whatever reason the addresses on E street and anything from 66TH Avenue through 80th Avenue. Note that these are not in one section of the data as the index is a BRT number which is based on many qualities of where the property is located in the city so for instance you could have 10 66th Avenue have the BRT number 101010101 and 8600 66th Avenue have the BRT 886890300.

Is there any reason besides corruption to why these will not come up as out of 92k records in the localities table these are the ONLY records that have any issues.

Please note that I also ran the same query in Access with the EXACT same results.

Any help would be much appreciated.

Thanks,

George Greiner

sql-server-2005queryuser-defined-function
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz avatar image graz ♦ commented ·
George, can you edit your post to include some sample data from both the table and the view and indicate whether you expect it to be in the final result?
0 Likes 0 ·
TG avatar image TG commented ·
DDL (for all dependent objects) and DML for at least one offending row would be ideal :)
0 Likes 0 ·
graz avatar image graz ♦ commented ·
What is the actual problem? Are rows now being excluded? Or are rows being included that should be?
0 Likes 0 ·

2 Answers

·
TG avatar image
TG answered

Without the benefit of sample data I would have to guess that those trouble rows are not exactly the same - ie: leading or trailing space(s) or some other non-printable character.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rm 1 avatar image
rm 1 answered

TRY THIS:

SELECT l.street, l.Number, l.Direction, l.suffix, l.Unit, l.LienNumber, l.LienType,             
l.DateField, l.Amount            
FROM Localities AS l            
LEFT JOIN vw_BRT v            
ON l.street = v.street            
AND l.Number = v.streetNum            
WHERE v.street IS NULL
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.