christian.dahl (7/22/2008)
Hi all!Im having a bit of a problem. I have to searvh a table to find how many rows who haves the same customerid(kunde_nr) as the customerid from a different table. So if my customer not exist in BOTH tables I do not want the customer in my result set. All the customers the result must have their customerid in both tables. Okay?
Heres how i have done it:
set @sql = @sql + ' and(select count(*) from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr) > 0'
If it counts more than 0 then the customer is valid.
The acctual sql statement is much bigger but that is the part making the trouble.
If I run this part alone in a query it returns the right result, but when it is used in the complete statement it returns wrong result. It returns +2 customers whom only exist in the A table and not in the CO55800T table as it must to be a part of the result.
Can anyone see what is wrong or where the problem might be?
You might want to try to determine if using an EXISTS clause yields the same problem. If it does then you know the COUNT itself isn't the problem, but something else. It would look something like this:
SET @SQL = @SQL + ' AND EXISTS (SELECT 0x00 FROM C055800T WHERE KUNDE_NR COLLATE SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
-Ben