July 22, 2008 at 7:09 am
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?
July 22, 2008 at 7:44 am
By the looks of your included T-SQL, you have gone about this in the wrong way.
Please post the entire SQL statement, the DDL for the tables, and some sample data if possible. Here are some tips for posting. If you follow these, you will get a MUCH better response.
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]
July 22, 2008 at 9:05 am
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
July 22, 2008 at 11:15 am
Already asked and answered here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116
N 56°04'39.16"
E 12°55'05.25"
July 23, 2008 at 1:15 am
the exists generates the same fault. It is really strange. Two customers who does not exists in the second table where the count and exists statement has been tried out is being returned to my page. The only customers who should be returned should be those who exists in both tables.
This is how it looks like now:
select A.EKUNDEID,
RTRIM(A.KUNDENR) as kundenr,
RTRIM(B.ANAVNB) as navn,
RTRIM(B.ABYB) as postnummerby,
RTRIM(A.AEMAIL) as email,
A.SAPKUNDENR,
B.AKONTAKT
from JB53000T A, JB53100T B
where B.EKUNDEID = A.EKUNDEID'
if(@kundenr <> '')
set @sql = @sql + ' and A.kundenr like ''' + @kundenr + ''''
...
...
other statements to complete the search for the customers
...
set @sql = @sql + ' and exists(select * from CO55800T where KUNDE_NR collate SQL_Danish_Pref_CP1_CI_AS = A.kundenr)'
...
exec(@sql)
July 23, 2008 at 1:32 am
I posted an answer here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107116
N 56°04'39.16"
E 12°55'05.25"
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply